an die SQL-Profis - ich krieg die Bedingung nicht hin

[heinz_ketchup]

Hallo liebe Helfenden,

für meine gewünschte Auswertung reicht mein SQL-Wissen leider noch nicht. Hier meine Problemstellung:

In meiner Datentabelle (cselling) habe ich Kundennummern (cs_kdnr), die unterschiedliche Erzeugnisse (cs_egrp) gekauft haben. Ich möchte nun eine Abfrage erstellen, die mir die Anzahl der Kunden zurück gibt, die in einem Jahr (Bsp. 2006) sowohl cs_egrp 1 als auch cs_egrp 2 gekauft haben. In meinem Beispiel sind das 3 Kunden (2, 4, 12). Also ohne Kunde 11, der ja nur cs_egrp 2 gekauft hat.

Hier mein Ansatz, der mich leider nicht weiter bringt:

SELECT COUNT(cs_kdnr),
FROM cselling WHERE cs_egrp = 1 OR cs_egrp = 2
GROUP BY cs_kdnr, cs_jahr HAVING cs_jahr = 2006;

Mit diesem Select bekomme ich leider nur die Anzahl aller Kunden pro Erzeugnisse und Jahr :-/

Ich danke euch schon für die Hilfe und freue mich auf eure Lösungsansätze.

Schöne Grüße, Werner

[Barlee]

Hallo heinz_ketchup,

es führen bestimmt einige Wege zum Ziel. Ich würde den hier dargestellten verfolgen, um die Kunden zu ermitteln, die beide Produkte gekauft haben.
Möchtest Du wirklich nur die Anzahl, dann könntest Du ein COUNT(*) außen herum machen:
(SQL direkt ausführen muss aktiviert sein)

Code: Alles auswählen

SELECT "cs_kdnr", "cs_Jahr"
FROM
(
SELECT DISTINCT "cs_kdnr", "cs_Jahr"
FROM "cselling" WHERE "cs_egrp" = 1 
UNION ALL
SELECT DISTINCT "cs_kdnr", "cs_Jahr"
FROM "cselling" WHERE "cs_egrp" = 2
)
GROUP BY "cs_kdnr", "cs_Jahr"
HAVING "cs_Jahr" = 2006 AND COUNT("cs_kdnr") =2

Viel Erfolg!
Gruß Barlee

[delta]

Hey, das ist echt cool, dass man Tabellen so schachteln kann. Ich lerne gerade SQL und das wusste ich bisher noch nicht. Ich hatte bei dem Problem zuerst an COUNT(DISTINCT ...) gedacht, was Barlees Lösung noch etwas kürzer macht (aber auch weniger anpassungsfähig):

Code: Alles auswählen

SELECT "cs_kdnr", "anzahl", "cs_jahr" FROM
(
     SELECT "cs_kdnr", "cs_jahr", COUNT(DISTINCT "cs_egrp") AS "anzahl"
     FROM "cselling"
     GROUP BY "cs_kdnr", "cs_jahr"
)
WHERE "anzahl" = 2

Die innere Tabelle allein listet übrigens auf, wie viele verschiedene "cs_egrp" ein Kunde pro Jahr gekauft hat.

[heinz_ketchup]

Hallo Barlee, hallo delta,

vielen Dank für eure Lösungsansätze. Für mich SQL-Laien sehen die schon sehr gut aus. Leider erhalte ich bei beiden Abfragen folgende Fehlermeldung:

"Every derived table must have its own alias"

:-/ ich habe schon ein bischen mit dem Alias "anzahl" von delta's Lösung rumprobiert. Leider hat es nichts genutzt. Und bei Barlee ist gar kein Alias drin. Fehlt da vielleicht eines?

Hier habe ich schon was zu dem Thema gefunden:
http://dev.mysql.com/doc/refman/5.0/en/ ... views.html

Nur bin ich nicht in der Lage es umzusetzen. Könnte ihr mir nochmal helfen? Das wäre nett.

Schöne Grüße, Werner

[delta]

Hm, also mit Base und dem implementierten HSQL hat das bei mir so funktioniert. Aber egal, es scheint wohl so, dass die Tabelle im inneren auch einen Namen bekommen muss. Es geht also nicht um ein Spaltenalias (wie mein "anzahl"), sondern um ein Tabellenalias. D.h. es müsste einfach nach dem FROM(...) noch ein "AS t1" kommen, also so:

Code: Alles auswählen

SELECT "cs_kdnr", "anzahl", "cs_jahr" FROM
(
     SELECT "cs_kdnr", "cs_jahr", COUNT(DISTINCT "cs_egrp") AS "anzahl"
     FROM "cselling"
     GROUP BY "cs_kdnr", "cs_jahr"
) AS t1
WHERE "anzahl" = 2

Versuch's mal so. Falls es immer noch nicht klappt, könntest du auch alternativ zwei Abfragen draus machen. Eine Abfrage (t1) mit dem eingerückte SQL-Code und dann eine zweite Abfrage, die eben diese Abfrage t1 benutzt, d.h. so:

Code: Alles auswählen

SELECT "cs_kdnr", "anzahl", "cs_jahr" FROM t1 WHERE "anzahl" = 2

Also mindestens eine Variante davon müsste klappen.

/edit:
Bei Barlees Lösung bin ich mir nicht sicher, was funktioniert. Ein einfaches "AS t1" nach FROM(...) funktioniert bei mir, bei mir hat aber auch schon die ursprüngliche Variante funktioniert. Auf der von dir verlinkten Seite wird gesagt, dass es so nicht ginge und es werden verschiedene andere Lösungen diskutiert. Die Lösungen erscheinen mir etwas unelegant, außer der letzten. Teste mal, ob's so klappt:

Code: Alles auswählen

SELECT "cs_kdnr", COUNT("cs_kdnr") AS "Anzahl", "cs_jahr"
FROM
(
    (SELECT DISTINCT "cs_kdnr", "cs_jahr" FROM "cselling" WHERE "cs_egrp" = 1)
     UNION ALL 
    (SELECT DISTINCT "cs_kdnr", "cs_jahr" FROM "cselling" WHERE "cs_egrp" = 2)
) AS t1
GROUP BY "cs_kdnr", "cs_jahr" HAVING "cs_jahr" = 2006 AND COUNT("cs_kdnr") = 2

Außer dem "AS t1" stehen da noch Klammern um die SELECTs. Ob das jetzt so einen großen Unterschied macht weiß ich nicht.

[heinz_ketchup]

Hallo delta,

danke für Deine schnelle Reaktion. Ich benutze eine MySQL und nicht Base. Daher wahrscheinlich die Empfindlichkeiten bei meiner Abfrage, die Du in HSQL nicht hast. Sorry, ich kenne die Unterschiede in den einzelnen SQL-Dialekten nicht.

Mit den Modifikationen klappen jetzt beide Abfragen. Leider bekomme ich mit Deiner Version nicht die richtigen Ergebnisse. Ich habe die Ergebnisse durch die Abfrage, vor meine manuell gefilterte Tabelle gelegt. Da sieht man die Unterschiede. Bei Deiner Abfrage bekomme z.B. eine Kundennr 8, die in 2006 garkein Produkt 2 (egrp) gekauft hat. Die Abfrage von Barlee funzt. Bitte verstehe das nicht als Kritik. Ich bekomme es ja garnicht hin.

Jetzt meine Frage: Wo muß ich in Barlee's Abfrage die Count-Funktion hinschreiben, wenn mein Ergebnis, nicht die Datensätze der betroffenen Kundennummern sein soll, sondern nur die Anzahl aller Kunden, die in 2006, Produkt 1 und Produkt 2 gekauft haben. Also in meinem Beispiel 1.985 .

Nochmals Danke und schöne Grüße, Werner

[Barlee]

Hallo Werner,

Stimmt - Du benutzt ja mySQL. Da war deltas Hinweis natürlich der entscheidende.
Zum COUNT():
Versuche bitte einmal

Code: Alles auswählen

SELECT COUNT(*) AS ANZAHL FROM
(
...hier die bisherige Abfrage rein
)

Gruß Barlee

[heinz_ketchup]

Hallo Barlee,

vielen Dank! Jetzt hat es geklappt.
Ich musste noch ein AS t2 zum Schluss anhängen und dann lief es.
Warum muss ich eigentlich bei MySQL nochmal jede Unterabfrage als eine neue Tabelle definieren, wenn ich dann nirgends auf das Alias Bezug nehme?

Code: Alles auswählen

SELECT COUNT(*) AS ANZAHL FROM
(
...hier die bisherige Abfrage rein
) AS t2

@ delta, auch an Dich vielen Dank, ohne Deine Ergänzungen, hätte ich das nicht hin bekommen. Konntest Du nochmal Deine Variante checken? Den Weg fand ich auch ganz elegant.

Schöne Grüße, Werner

[heinz_ketchup]

Hallo Barlee,

jetzt dachte ich, das wäre schon die Lösung gewesen, aber nun ist ein neues Problem aufgetaucht. Ich habe mittlerweile Deine Abfrage, von der Grundlage her verstanden.

In meiner Tabelle sind natürlich nicht nur Erzeugnisgruppen 1 und 2. Sondern es geht bis 7.

Wenn ich jetzt Deine Abfrage:

Code: Alles auswählen

SELECT cs_kdnr, cs_jahr
FROM
(
SELECT DISTINCT cs_kdnr, cs_jahr
FROM cselling WHERE cs_egrp = 1 
UNION ALL
SELECT DISTINCT cs_kdnr, cs_jahr
FROM cselling WHERE cs_egrp = 2
) AS t1
GROUP BY cs_kdnr, cs_jahr
HAVING cs_jahr = 2006 AND COUNT(cs_kdnr) = 2;

nehme und einfach Erzeugnisgruppe 2 durch Erzeugnisgruppe 3 ersetze

Code: Alles auswählen

SELECT cs_kdnr, cs_jahr
FROM
(
SELECT DISTINCT cs_kdnr, cs_jahr
FROM cselling WHERE cs_egrp = 1 
UNION ALL
SELECT DISTINCT cs_kdnr, cs_jahr
FROM cselling WHERE cs_egrp = 3
) AS t1
GROUP BY cs_kdnr, cs_jahr
HAVING cs_jahr = 2006 AND COUNT(cs_kdnr) = 3;

dann erhalte ich keinen einzigen Datensatz, obwohl in meiner Ausgangstabelle natürlich auch Kunden sind die sowohl 1 als auch 3 in 2006 gekauft haben. Wo liegt mein Fehler?

Außerdem habe ich noch nicht verstanden, warum Du zum Schluss nochmal einen COUNT(cs_kdnr) = 2 setzt?

Danke für Deine Hilfe und schöne Grüße
Werner

[Barlee]

Hallo Werner,

Dein Fehler liegt hier:

Code: Alles auswählen

COUNT(cs_kdnr) = 3;

Suchst Du Kunden, die die Produkte 1 und 3 gekauft haben, dann muss dieser Wert weiterhin 2 sein. Warum?
Die beiden inneren, durch UNION verbundenen, SELECT suchen die Kunden, die beide Produkte gekauft haben. Dabei gibt das erste SELECT eine Ergebnistabelle für Produkt 1, die zweite für Produkt 3 zurück. Beide Ergebnistabellen werden durch UNION verbunden, so dass eine Gesamtergebnistabelle entsteht. Dabei ergeben sich folgende Konstellationen.

Kunde hat nur Produkt 1 gekauft >> Kdnr erscheint nur in der ersten Tabelle >> Kdnr nur einmal vorhanden
Kunde hat nur Produkt 3 gekauft >> Kdnr erscheint nur in der zweiten Tabelle >> Kdnr nur einmal vorhanden
Kunde hat beide Produkte gakauft >> Kdnr kommt in beiden Ergebnistabelllen vor >> Kdnr zweimal vorhanden

Die DISTINCTS sorgen dafür, dass die Kombination cs_Jahr, cs_Kdnr nur einmal in den Teilergebnistabellen vorhanden sein kann.
Somit gilt - und da kommen wir zu Deiner Frage:

Außerdem habe ich noch nicht verstanden, warum Du zum Schluss nochmal einen COUNT(cs_kdnr) = 2 setzt

Wenn in der UNION Ergebnistabelle für ein angegebenes Jahr (was Du über die HAVING Klausel einschränkst) eine Kundennummer zweimal vorkommt, dann muss sie in beiden Teiltabellen genau einmal vorhanden sein. Das wiederum bedeutet, dass der Kunde Produkt 1 und Produkt 2
gekauft hat. Ich hoffe, es ist verständlich geworden?

-----------

Code: Alles auswählen

COUNT(cs_kdnr) = 3;

Wäre nur in dem Fall richtig, wenn Du nach Kunden suchst, die 3 Produkte gekauft haben. Das erfordert dann aber auch das Einfügen eines weiteren SELECTS in die UNION

Code: Alles auswählen

(
SELECT DISTINCT cs_kdnr, cs_jahr
FROM cselling WHERE cs_egrp = 1
UNION ALL
SELECT DISTINCT cs_kdnr, cs_jahr
FROM cselling WHERE cs_egrp = 3
UNION ALL
SELECT DISTINCT cs_kdnr, cs_jahr
FROM cselling WHERE cs_egrp = 4
) AS t1

Gruß Barlee

[heinz_ketchup]

Hallo Barlee,

vielen Dank für Deine Erläuterungen! Das war sehr klar dargestellt und ich hab's jetzt auch kapiert. Nun funktionieren auch alle Auswertungsvarianten, die ich mir so vorgestellt hatte.

Ich habe wieder mal viel von Dir gelernt.

Schöne Grüße
Werner