Subqueries inMySQL

[BennoAchenbach]

Ich versuche verzweifelt, eine Query mit Subqueries zu erstellen und finde einfach kleine Lösung. Ich bin kein Programmierer, sondern schreibe mit lediglich hin und wieder Abfragen auf einer bestehenden Datenbank.

In einer Tabelle sollen alle Datensätze ermittelt werden, die einer Bedingung entsprechen, also ein einfaches Count(*). Jetzt möchte ich diese Abfrage noch aufteilen in Subqueries und da scheitere ich.


Das Script sieht dann so aus

select jahr, count(*),
(select count(*) from daten where jahr>2020 and kennz like 'E%' and art='GR 1' ) as 'G1'
from daten
where jahr>2020 and daten like 'E%'
group by jahr
order by jahr;

Das Ergebnis sieht so aus. Die erste Spalte ist vom Wert korrekt, die zweite Spalte stimmt nicht mehr. Es fehlt nach meinem Verständnis ein group by jahr, damit die Zahlen korrekt berechnet werden.

Anmerkung: Wenn das Script funktioniert, besteteht es aus ca 10 Subqueries

+------+----------+------+
| jahr | count(*) | G1 |
+------+----------+------+
| 2021 | 534 | 146 |
| 2022 | 594 | 146 |
| 2023 | 614 | 146 |
| 2024 | 644 | 146 |
| 2025 | 162 | 146 |
+------+----------+------+

Aber wenn ich ein group by jahr einfüge, bekomme ich folgende Fehlermeldung.

(select count(*) from daten where jahr>2020 and kennz like 'E%' and status='GR 1' group by jahr) as 'G1'

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Was mache ich falsch?

 

[ukulele]

Nun fangen wir mal mit der Fehlermeldung an: Die bezieht sich auf dein * im Select-Teil des Subquerys. Beim GROUP BY muss jede Spalte entweder aggregiert oder gruppiert werden, das schließt ein * defacto aus. Selbst wenn das funktionieren würde, wäre das aber nicht das Ergebnis, das du suchst ;-)

Hier mal, wie es gehen könnte (Zugriff aus dem Subquery auf Informationen aus dem Hauptquery):

select jahr, count(*),
(select count(*) from daten d where d.jahr=daten.jahr and d.kennz like 'E%' and d.art='GR 1' ) as 'G1'
from daten
where jahr>2020 and daten like 'E%'
group by jahr
order by jahr;

Außerdem sind Subquerys schlechter Stil und es gibt bessere Wege, das umzusetzen:

select jahr, count(*),
sum(case when d.art='GR 1' then 1 else 0 end) as 'G1'
from daten
where jahr>2020 and daten like 'E%'
group by jahr
order by jahr;

Eventuell gibt es filter() - da ich aber aus der MSSQL Schiene komme bin ich jetzt nicht sicher, ob das geht.

 

[BennoAchenbach]

Danke für die schnelle Antwort. Funktioniert super und jetzt kann ich mir damit diverse Abfragen bauen. Mit "case when" hatte ich noch nie gearbeitet, aber das sieht für mich sehr praktikabel aus.

Ein Problem habe ich noch. Es gibt Daten ohne Kennzeichen und Kennzeichen, die wenige Male vergeben wurden. Dafür möchte ich eine Sammelspalte machen. Einfach die Definierten Spalten in einer "Not in"-Klausel zusammen zu fassen und dann als alle anderen zu definieren, funktioniert leider nicht. In grauer Vorzeit hatte mir mal ein Datenbanker gesagt, wie man das macht, aber ich habs vergessen.

sum(case when d.kennz not in ('GR 1, 'GR 2', 'Gr 3') then 1 else 0 end) as 'Sonst'

 

[ukulele]

Eigentlich gibt es an deinem CASE nicht viel auszusetzen, "funktioniert leider nicht" ist leider etwas unspezifisch Vermutlich hast du NULL-Werte, die kann man mit NOT IN nicht prüfen, das Ergebnis ist dann NULL. Du könntest aber z.B.

sum(case when d.kennz is null or d.kennz not in ('GR 1, 'GR 2', 'Gr 3') then 1 else 0 end) as 'Sonst'

oder

sum(case when isnull(d.kennz,'') not in ('GR 1, 'GR 2', 'Gr 3') then 1 else 0 end) as 'Sonst'

nutzen.

 

[BennoAchenbach]

"Funktioniert leider nicht" weil ich mit den Zahlen, die ausgegeben wurden, rein gar nichts anfangen konnte.
Mir war nicht klar, daß Felder mit dem Wert NULL nicht gezählt werden. Habe jetzt mal testweise NULL mit '' ersetzt und die Zahl stimmt exakt! Dankeschön!
Jetzt habe ich endlich einen sehr übersichtlichen Weg gefunden, meine Daten einfach auszuwerten. Jetzt geht es ans Eingemachte und Auswertungen über mehrere Tabellen. Mal gucken, wann ich wieder SOS funken muß,

 

[ukulele]

Ja es gibt manchmal Stellen, wo man sich die Eigenschaften von NULL vor Augen führen sollte. NULL + Etwas ist immer NULL. count() zählt aber z.B. NULL mit, sum() ignoriert NULL und avg(), ja da weiß ich grade schon selbst nicht mehr ;-) In deinem Fall liefert halt NULL NOT IN () auch nur NULL, dann greift ELSE 0 und sum() rechnet 0. Man könnte das auch umstellen, man muss sich halt nur klar machen, was wann das als Ergebnis raus kommt.

 

[dabadepdu]

Ja es gibt manchmal Stellen, wo man sich die Eigenschaften von NULL vor Augen führen sollte.

Und das lohnt sich im wahrsten Sinne des Wortes.
Wenn man sich den ganzen Apparat anschaut, den dieser (Nicht)Wert bei der Arbeit mit SQL nach sich zieht, Schreibaufwand, Behandlung von Sonderfällen in der Aggregation und Filterung, dann ist das eine der meist vernachlässigten Stellen im Datenmodell.
Man tut sich wirklich selbst einen großen Gefallen, die Auswirkungen von NULL Spalten immer wieder zu prüfen, vor allem beim ersten Aufschlag.
Beginnend mit der Frage:
Was bedeutet NULL im Datensatz/ Business-Case konkret, u.U. in Kombination mit anderen Feldwerten?
- Wert unbekannt, nicht verfügbar?
- nicht eingetragen, weil es erlaubt ist, nichts einzutragen? (Wäre also bekannt, hat aber 0,2 Sekunden Zeit gespart, es nicht zu tun- falls überhaupt manuell erfasst)
- Synonym zu 0, "", Christi Geburt, Urknall, Unix Time 0 (1.1.1970)?
- In welchem Verhältnis steht NULL zum Datentyp und was sind die Implikationen (Datum, String, Int, Float, BLOB)
- Stellt die Nullable Spalte einen Sekundärschlüssel dar?
- ...

zur letzten Frage:
Wenn es vermeidbar ist, keine Referenz einzutragen und man damit nichts gewaltsam verbiegt, sollte man eine Spalte mandatory definieren.
Dann hat man keine Outer Joins (wäre quasi dumm, unnötig, sogar besser noch verboten, sie zu nutzen). Die Folge wäre bessere Performance, bessere Wirksamkeit von Filtern und im Ergebnis natürlich eine präzisere Datenlage (Wenn der erzwungene Eintrag nicht einen Pferdefuß hat und zur Falscheingabe animiert. Kennt bestimmt jeder "Da musst du einfach irgendwas eintragen"). Handelt es sich nicht um Referenzen zwischen Core Tables, sondern geht es "nur" um lästige Kategorien, könnte man meinen, nicht so wichtig. Aber gerade hier kann ich eine Menge aus NULL machen. Nehmen wir "Wert unbekannt" versus "(noch) nicht angegeben". Das ist offensichtlich ein fundamentaler Unterschied. Die beiden Fälle kann man explizit in die Kategorietabelle aufnehmen, ebenso wie bspw. "undefinierte Kategorie". Wenn man auf die Art hinreichend explizite Werte definiert, muss es vielleicht keine Nullable Spalte mehr sein. Dann ist mit mit den bereits genannten Vorteilen einer Mandatory Spalte empfehlenswert.