GROUP BY und AGGREGAT-Funktionen

[patrick]

Hallo zusammen,

ich hänge hier an einem SQL-Statement und komme nicht weiter bzw. Ich bin mir nicht sicher, ob man das überhaupt mit einer einzigen DB-Anfrage hinbekommt.... das ist nämlich mein Ziel.

Dies ist meine DB-Struktur:

Projekt
------
ID
NAME
STATUS_ID
KATEGORIE_ID

Kategorie
--------
ID
NAME

Status
-----
ID
NAME

So, ich hätte jetzt gerne alle Kategorien mit der Anzahl der darin vorhandenen Projekte:

SELECT kategorie.name, COUNT(projekt.id) AS projekte_in_kat FROM kategorie, projekt WHERE kategorie.id = projekt.kategorie_id GROUP BY projekt.kategorie_id

Das funktioniert auch.
Zusätzlich interessiert mich noch die Anzahl der Projekte je Status (in jeder einzelnen Kategorie). Also das Ergebnis sollte etwa so ausshen:

Kategorie.name      projekte_in_kat    projekte_status_a    projekte_status_b
---------------------------------------------------------------
Software               41                    11                      30
Hardware                25                    12                     13

Hat da jemand einen Ansatz?
Vielen Dank schon mal im Voraus,

Patrick.

[Cheatah]

Hi,

Projekt

ID
NAME
STATUS_ID
KATEGORIE_ID
Zusätzlich interessiert mich noch die Anzahl der Projekte je Status (in jeder einzelnen Kategorie).

vor Ausführung des Statements muss jede einzelne Spalte bekannt sein. Wenn Du also weißt, dass nur STATUS_ID=a und STATUS_ID=b existieren, kannst Du gezielt darauf hin selektieren; im schlimmsten Fall wirst Du über Deine Ergebnismenge noch einmal gruppiert selektieren müssen.

Sind die Spalten nicht vorher bekannt (oder sogar je sich ergebenden Datensatz unterschiedlich), ist SQL nicht die Technik Deiner Wahl.

Cheatah

[Ilja]

yo,

Hat da jemand einen Ansatz?

in der form, dass du die anzahl der verschiedenen status werte in der projektion abbbilden willst, sprich nebeneinander in der SELECt Klausel, geht es nur, wenn du alle status-werte kennst und diese dürfen sich dann auch nicht verändern. ändert sich die status-tabelle, zum beispiel weil ein wert dazu kommt oder wegfällt, dann musst du die abfrage anpassen und das ist unschön. es gibt eventuell noch möglichkeiten, sich auch davon unabhängig zu machen, das macht die abfrage aber erheblich komplizierter. das hängt aber auch jeweils von dem verwendendeten dbms ab.

einfacher würde es gehen, wenn du die anzahl der jeweiligen statuswerte untereinander anzeigen würdest.

Ilja

[Sven Rautenberg]

Moin!

Zusätzlich interessiert mich noch die Anzahl der Projekte je Status (in jeder einzelnen Kategorie). Also das Ergebnis sollte etwa so ausshen:

Kategorie.name      projekte_in_kat    projekte_status_a    projekte_status_b

Software               41                    11                      30
Hardware                25                    12                     13

Wie dir schon zwei Leute gesagt haben, kriegst du die von dir gewünschte Anzeige nur hin, wenn du die Status kennst, die angezeigt werden sollen.

Dass du aber eine eigenständige Tabelle für Status führst, deutet eher darauf hin, dass du diese eben NICHT kennst, weil sie dynamisch in der Tabelle jederzeit geändert werden könnten.

Insofern bleibt dir eigentlich nur, den Plan "Kategorie, Anzahl und Statusanzahl in einer Zeile" aufzugeben, daraus wird mit SQL nichts werden.

Was das Gruppieren angeht: Wenn du einen SQL-Query suchst, dann erstelle zuerst den Teil, der dir alle benötigten Daten aus den Tabellen einsammelt, mit allen JOINs etc., und erst im zweiten Schritt gruppierst du.

SELECT kategorie_id, status_id FROM projekt

Das liefert dir eine Liste. Du willst die Summe aller Status je Projekt und auch die Summe je Kategorie. SQL kann nur summieren, wo beides übereinstimmt, also gleichzeitig Kategorie und Status identisch ist:

SELECT kategorie_id, status_id, count(*) as zahl FROM projekt GROUP BY kategorie_id, status_id

Das liefert dir pro eindeutigem Pärchen Kategorie/Status die Summe dieser Kombination.

Wenn du für alle Zeilen, in denen die Kategorie identisch ist, die Summe bildest, hast du die Anzahl der Kategorien. MySQL ab 4.1.1 kann das aber für dich übernehmen:

SELECT kategorie_id, status_id, count(*) as zahl FROM projekt GROUP BY kategorie_id, status_id WITH ROLLUP

Siehe http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html

Dann kriegst du am Ende einer Kategorie noch eine Summenzeile, in der die status_id auf NULL gesetzt ist.

Ein Problem hat dein Tabellenlayout noch, und das wird in der Abfrage deutlich: Du kannst leider nicht ermitteln, welche Projekt-ID und welchen Namen deine "Summe" hat. Du kannst nur ermitteln, wieviele Projekte einer Kategorie und einem Status angehören.

- Sven Rautenberg