MYSQL count mit Ausgabe 0 gewünscht

[ASchueltke]

Hallo zusammen,

ich hab gerade irgendwie n Hänger und komme nicht weiter.
Eigentlich ist das Problem simpel, hört sich zumindest für mich so an.
Aber heute komm ich nicht weiter, obwohl SQL - naja nicht täglich Brot aber -schon öfters von mir verwendet wird.

Ich möchte zu jeder Status Bezeichnung (Tabelle: status) die Anzahl des jeweiligen Vorkommens in Tabelle daten
UND wenn keiner gefunden wurde soll da die 0 oder NULL stehen. Sprich alle bez aus status sollen ausgegeben werden plus zahl.

tb: status
id bez
1 status1
2 status2
3 status3

tb:daten
id status datum
1 status1 2013-06-06
2 status1 2013-06-06
3 status2 2013-06-06
4 status2 2013-06-06
5 status2 2013-06-05


Als Ergebnis will ich:
status1 2
status2 3
status3 0

Mein bester Versuch war

SELECT status.bez, count(daten.id) FROM status
LEFT join daten ON daten.status = status.bez
GROUP by status.bez

Da funktioniert es, allerdings wenn ich das Datum mit einbeziehe nicht mehr

SELECT status.bez, count(daten.id) FROM status
LEFT join daten ON daten.status = status.bez
WHERE daten.datum LIKE '2013-06%'
GROUP by status.bez

Funktioniert auch bis auf die gewünschte Ausgabe status3 mit 0 oder auch NULL

Vielleicht kann mir ja einer auf die Sprünge helfen.

Danke und viele Grüße aus dem Sauerland
Andre

 

[Yaslaw]

Ersetze COUNT(daten.id) durch COUNT(*)

COUNT(expr)

Gibt die Anzahl der Nicht-NULL-Werte in den von einer SELECT-Anweisung abgerufenen Datensätzen an.

COUNT(*) ist dahingehend ein wenig anders, dass es die Anzahl der abgerufenen Datensätze unabhängig davon angibt, ob diese NULL-Werte enthält oder nicht.

 

[ASchueltke]

Hi Yaslaw und danke für die schnelle Antwort.

Leider keiner Veränderung mi:

SELECT status.bez, count(*) FROM status
LEFT join daten ON daten.status = status.bez
WHERE daten.datum LIKE '2013-06%'
GROUP by status.bez

Der status3 bleibt unsichtbar :-(

Grüße

 

[Yaslaw]

Sorry, hab den WHERE übersehen.

WHERE daten.datum LIKE '2013-06%'

Wenn daten.datum NULL ist, weil zum Status 3 keine daten vorhanden sind, fallen diese raus. Du kannst es ev. so lösen

WHERE IFNULL(daten.datum, '2013-06') LIKE '2013-06%'

 

[ASchueltke]

In diesem Fall zählt er dann aber auch die Anzahl für status3 auf 1 hoch.
Und sobald ich den Wert von IFNULL auf einen Bereich außerhalb des LIKES anwende, wird status3 wieder nicht ausgegeben

Bspw:

WHERE IFNULL(daten.datum, '2013-05') LIKE '2013-06%'

Kniffelig ...

 

[ASchueltke]

Die Lösung sieht nun folgender Maßen aus:

SELECT status.bez, IFNULL( COUNT( t.id ) , 0 )
FROM status
LEFT JOIN daten ON daten.status = status.bez
AND daten.datum LIKE '2013-06%'
GROUP BY status.bez

Find ich gut und auch verständlich, aber wieso reagiert er bei dem WHERE anders als bei dem AND.
Wenn ich den Code jetzt erklären müsste, würde ich bei AND und WHERE immer das gleiche sagen.

Macht WHERE hier nochmal ein Art weiter ausholende Datumsabgrenzung?

Grüße
Andre

 

[MPr]

Hallo,

ausgehend von der Annahme, dass sich MySQL ungefähr an die üblichen SQL-Regeln hält (was möglicherweise nicht die robusteste Annahme ist):

- das AND gehört zur ON clause: es definiert, welche Daten im Join zu verknüpfen sind
- eine WHERE-Bedingung wirkt sich auf das Resultat des Joins aus und filtert die gelieferten Daten

Insofern ist ein Outer Join der Form:

tab1 left outer join tab2 on tab1.col1 = tab2.col1 where tab2.col2 = '42'

kein Outer Join mehr, sondern ein inner join, da man einerseits zwar NULL-Werte auf der Seite von tab2 ergänzen möchte, aber gleichzeitig fordert, dass für die tab2-Seite eine zusätzliche Bedingung gelten soll, was die NULLs wieder ausschließt, weil NULL nicht gleich 42 ist bzw. sein kann. Insofern ist es auch ganz gleichgültig, welche zusätzliche Bedingung man für die outer join Tabelle ergänzt. Im vorliegenden Fall würde auch die folgende - für jeden Datensatz in "daten" gültige - Bedingung den outer join ausschalten:

SELECT STATUS.bez, COUNT(*) FROM STATUS
LEFT JOIN daten ON daten.STATUS = STATUS.bez
WHERE daten.id > 0
GROUP BY STATUS.bez

+---------+----------+
| bez     | COUNT(*) |
+---------+----------+
| status1 |        2 |
| status2 |        3 |
+---------+----------+

Übrigens dürfte das IFNULL in der endgültigen Query überflüssig sein:

SELECT STATUS.bez, COUNT( daten.id )
FROM STATUS
LEFT JOIN daten ON daten.STATUS = STATUS.bez
AND daten.datum LIKE '2013-06%'
GROUP BY STATUS.bez;

+---------+-------------------+
| bez     | COUNT( daten.id ) |
+---------+-------------------+
| status1 |                 2 |
| status2 |                 3 |
| status3 |                 0 |
+---------+-------------------+

Gruß

Martin

 

[ASchueltke]

Nun wird es wieder kniffeliger, da ich jetzt eine Tabelle dazwischen habe.
Und zwar bekommt ein Datensatz in seiner Laufbahn verschiedene Stati.
Die will ich zählen je nach Datumsbereich

Folgende Tabellenübersicht haben wir jetzt

tb:status
id bez
1 status1
2 status2
3 status3

tb:daten
id datum
1 2013-06-06
2 2013-06-06
3 2013-06-06
4 2013-06-06
5 2013-06-05

tb:datINstat
statID, datenID
1 1
2 1
1 2
2 2
1 3
1 4
2 5
3 5

Als Ergebnis will ich ( da ich nur 2013-06-06 will):
status1 4
status2 2
status3 0

Ich bekomme als Ergebnis immer nur die verwendeten Stati angezeigt,

SELECT STATUS.bez, IFNULL( COUNT( datINstat.statID ) , 0 )
FROM STATUS
LEFT JOIN datINstat ON STATUS.id = datINstat.statID
LEFT JOIN daten ON datINstat.datenID = daten.id
WHERE daten.datum = '2013-06-06'
GROUP BY STATUS.bez

oder alle Stati mit Zahl aber dann greift die Datumsbegrenzung nicht

SELECT STATUS.bez, IFNULL( COUNT( datINstat.statID ) , 0 )
FROM STATUS
LEFT JOIN datINstat ON STATUS.id = datINstat.statID
LEFT JOIN daten ON datINstat.datenID = daten.id
AND daten.datum = '2013-06-06'
GROUP BY STATUS.bez

 

[MPr]

die Datumseinschränkung könnte man wahrscheinlich in die Gruppenfunktion integrieren:

count(case when daten.datum = '2013-06-06' then 1 else null end)

Damit werden nur die Fälle gezählt, in denen das Datum dem Vergleichswert entspricht, aber die relevanten Gruppen werden nicht eingeschränkt.

Gruß

Martin