Group by Datensatz mit nur einem Vorkommen ermittel

Question

Ich habe in einer Tabelle solche Daten

29.12.2018 Müller Cola
29.12.2018 Maier Bier
29.12.2018 Maier Bier
29.12.2018 Huber Cola
29.12.2018 Huber Bier
29.12.2018 Maier Cola
29.12.2018 Irmler Cola
29.12.2018 Irmler Cola
29.12.2018 Irmler Bier
29.12.2018 Ruf Cola
29.12.2018 Müller Bier
29.12.2018 Huber Schnaps
30.12.2018 Müller Bier
30.12.2018 Müller Bier
30.12.2018 Maier Bier

Ich suche jetzt die Namen die an einem Tag genau ein Bier und eine Cola getrunken haben.
Also oben: Müller (29.12.2018 ein Bier und eine Cola) Huber nicht, da ja auch einen Schnaps getrunken. Maier nicht da am 29.12 zwei Bier

Hat jemand eine Idee?

Bislang bekomme ich nur raus wieviele einer an einem Tag getrunken hat, aber wie grenze ich die aus, die am selben Tag auch einen Schnaps getrunken haben? 

---

rainerkerner

Answer 1

Select Tag, Name, Getränk, count(*)
from Bestellung
where Getränk in ('Cola', 'Bier')
group by Tag, Name, Getränk
having count(*) = 1

Meinst du in etwa so?

Answer 2

Danke für den Vorschlag.

Nein, das passt so nicht, da würde ich den Huber ja auch ermitteln. Der hat aber einen Schnaps getrunken, den will ich nicht. Ich will alle, die genau 1 Cola und 1 Bier und nichts anderes getrunken haben

---

rainerkerner

Answer 3

Hi,

so schnell dahingeschrieben (und sicherlich auch einfacher zu machen, bin nur grad etwas in Eile) in etwa so (kommt auf jeden Fall mal das raus, was Du wolltest^^)

WITH ColaQuery AS
(
SELECT   Datum,
         Gast,
         Getraenk,
         COUNT( 1 ) AS Anzahl
FROM     RainerKerner_Trinken
WHERE    Getraenk = 'Cola'
GROUP BY Datum,
         Gast,
         Getraenk
HAVING   COUNT( 1 ) = 1
), BierQuery AS
(
SELECT   Datum,
         Gast,
         Getraenk,
         COUNT( 1 ) AS Anzahl
FROM     RainerKerner_Trinken
WHERE    Getraenk = 'Bier'
GROUP BY Datum,
         Gast,
         Getraenk
HAVING   COUNT( 1 ) = 1
), SonstigesQuery AS
(
SELECT   Datum,
         Gast,
         Getraenk,
         COUNT( 1 ) AS Anzahl
FROM     RainerKerner_Trinken
WHERE    Getraenk NOT IN ( 'Cola', 'Bier' )
GROUP BY Datum,
         Gast,
         Getraenk
), JoinedQuery AS
(
SELECT cq.Datum,
       cq.Gast
FROM   ColaQuery cq
       INNER JOIN BierQuery bq ON bq.Datum = cq.Datum AND bq.Gast = cq.Gast
)
SELECT *
FROM   JoinedQuery jq
       LEFT JOIN SonstigesQuery sq ON jq.Datum = sq.Datum AND jq.Gast = sq.Gast
WHERE  sq.Datum IS NULL

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

Answer 4

Ok, da kann man dann noch ergänzen:

select * from (

Select Tag, Name, Getränk, count(*)
from Bestellung a
where Getränk in ('Cola', 'Bier')
group by Tag, Name, Getränk
having count(*) = 1

) x

where not exists (select * from Bestellung b where x.Name = b.Name and b.Getränk not in ('Cola', 'Bier'))

Answer 5

Hallo Rainer,

USE tempdb
GO
SET NOCOUNT ON
GO

CREATE TABLE tbltemp(
  Datum date not null,
  Person varchar (20),
  Getraenk varchar (20)
)
GO

INSERT INTO tbltemp(Datum, Person, Getraenk)
  SELECT '29.12.2018', 'Müller', 'Bier'UNION ALL
  SELECT '29.12.2018', 'Maier', 'Bier'UNION ALL
  SELECT '29.12.2018', 'Maier', 'Bier'UNION ALL
  SELECT '29.12.2018', 'Huber', 'Cola'UNION ALL
  SELECT '29.12.2018', 'Huber', 'Bier'UNION ALL
  SELECT '29.12.2018', 'Maier', 'Cola'UNION ALL
  SELECT '29.12.2018', 'Irmler', 'Cola'UNION ALL
  SELECT '29.12.2018', 'Irmler', 'Cola'UNION ALL
  SELECT '29.12.2018', 'Irmler', 'Bier'UNION ALL
  SELECT '29.12.2018', 'Ruf', 'Cola'UNION ALL
  SELECT '29.12.2018', 'Müller', 'Bier'UNION ALL
  SELECT '29.12.2018', 'Huber', 'Schnaps'UNION ALL
  SELECT '30.12.2018', 'Müller', 'Bier'UNION ALL
  SELECT '30.12.2018', 'Müller', 'Bier'UNION ALL
  SELECT '30.12.2018', 'Maier', 'Bier' UNION ALL
  SELECT '30.12.2018', 'Diesel', 'Bier' UNION ALL
  SELECT '30.12.2018', 'Diesel', 'Cola' 

GO

WITH CTE AS
(
SELECT DISTINCT Datum, Person,
	SUBSTRING(MYCOMMASEPARATEDLIST, 2, 8000) AS Getraenke
  FROM tbltemp c 
  CROSS APPLY
  (SELECT '; ' + CAST(Getraenk AS varchar(255)) 
    FROM tbltemp o
   WHERE o.datum = c.datum AND o.Person = c.Person
   ORDER BY o.datum, o.Getraenk
     FOR XML PATH('')) AS x(MyCommaSeparatedList)
)

SELECT * 
FROM CTE
WHERE Getraenke = ' Bier; Cola'  /* Filter setzen; auskommentieren für gesamte Getränkeanzeige einer Person an einem Tag */

GO

DROP TABLE tbltemp
GO 

Schönen Abend.

Answer 6

Hallo Rainer,

hier noch ein anderer Vorschlag (es gibt sicher noch viele weitere Möglichkeiten):

USE tempdb
SET NOCOUNT ON

CREATE TABLE tbltemp(
  Datum date not null,
  Person varchar (20),
  Getraenk varchar (20)
)

INSERT INTO tbltemp(Datum, Person, Getraenk)
  SELECT '29.12.2018', 'Müller', 'Bier' UNION ALL
  SELECT '29.12.2018', 'Maier', 'wodka' UNION ALL
  SELECT '29.12.2018', 'Maier', 'Schnaps' UNION ALL
  SELECT '29.12.2018', 'Huber', 'Cola' UNION ALL
  SELECT '29.12.2018', 'Huber', 'Bier' UNION ALL
  SELECT '29.12.2018', 'Maier', 'Cola' UNION ALL
  SELECT '29.12.2018', 'Irmler', 'Cola' UNION ALL
  SELECT '29.12.2018', 'Irmler', 'Cola' UNION ALL
  SELECT '29.12.2018', 'Irmler', 'Bier' UNION ALL
  SELECT '29.12.2018', 'Ruf', 'Cola' UNION ALL
  SELECT '29.12.2018', 'Müller', 'Bier' UNION ALL
  SELECT '29.12.2018', 'Huber', 'Schnaps' UNION ALL
  SELECT '30.12.2018', 'Müller', 'Bier' UNION ALL
  SELECT '30.12.2018', 'Müller', 'Bier' UNION ALL
  SELECT '30.12.2018', 'Maier', 'Bier' UNION ALL
  SELECT '30.12.2018', 'Diesel', 'Bier' UNION ALL
  SELECT '30.12.2018', 'Diesel', 'Cola' 



SELECT *
FROM
(
    SELECT Datum, Person,
        SUM(CASE WHEN Getraenk = 'Cola' THEN 1 ELSE 0 END) AS Anzahl_Cola,
        SUM(CASE WHEN Getraenk = 'Bier' THEN 1 ELSE 0 END) AS Anzahl_Bier,
        SUM(CASE WHEN Getraenk NOT IN ('Cola', 'Bier') THEN 1 ELSE 0 END) AS Anzahl_Sonstiges
    FROM tbltemp
    GROUP BY Datum, Person
) AS t1
WHERE Anzahl_Cola = 1 AND Anzahl_Bier = 1 AND Anzahl_Sonstiges = 0  /* Filter setzen; auskommentieren für gesamte Getränkeanzeige einer Person an einem Tag */

ORDER BY datum, person

DROP TABLE tbltemp

Schönen Tag.

Answer 7

Vielen Dank an alle.

Ihr habt mir echt weitergeholfen.

---

rainerkerner