OpenOffice.info

SELECT "Name", 1 "Note", SUM(CASE WHEN "Note" = 1 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"
UNION
SELECT "Name", 2 "Note", SUM(CASE WHEN "Note" = 2 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"
UNION
SELECT "Name", 3 "Note", SUM(CASE WHEN "Note" = 3 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"
UNION
SELECT "Name", 4 "Note", SUM(CASE WHEN "Note" = 4 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"
UNION
SELECT "Name", 5 "Note", SUM(CASE WHEN "Note" = 5 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"
UNION
SELECT "Name", 6 "Note", SUM(CASE WHEN "Note" = 6 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"

SELECT 
	"Name",
	"Note",
	SUM(CASE WHEN "Noten"."Note" = "Klasse"."Note" THEN 1 ELSE 0 END) "Anzahl"
FROM 
	"Noten" JOIN "Klasse" ON TRUE
GROUP BY 
	"Name","Note"
ORDER BY
	"Name","Note"

SELECT
	"Name",
	SUM(CASE WHEN "Note" = 1 THEN 1 ELSE 0 END) "Anzahl 1er",
	SUM(CASE WHEN "Note" = 2 THEN 1 ELSE 0 END) "Anzahl 2er",
	SUM(CASE WHEN "Note" = 3 THEN 1 ELSE 0 END) "Anzahl 3er",
	SUM(CASE WHEN "Note" = 4 THEN 1 ELSE 0 END) "Anzahl 4er",
	SUM(CASE WHEN "Note" = 5 THEN 1 ELSE 0 END) "Anzahl 5er",
	SUM(CASE WHEN "Note" = 6 THEN 1 ELSE 0 END) "Anzahl 6er",
	AVG("Note"*1.0) "Durchschnitt"
FROM
	"Klasse"
GROUP BY
	"Name"

Servus

Für Dein Ziel gibt es mit der HSQLDB leider keine schlichte Lösung.
Um die von Dir gewünschte Darstellung zu erreichen, hast Du trotzdem mehrere Möglichkeiten, z.B.
[code]SELECT "Name", 1 "Note", SUM(CASE WHEN "Note" = 1 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"
UNION
SELECT "Name", 2 "Note", SUM(CASE WHEN "Note" = 2 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"
UNION
SELECT "Name", 3 "Note", SUM(CASE WHEN "Note" = 3 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"
UNION
SELECT "Name", 4 "Note", SUM(CASE WHEN "Note" = 4 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"
UNION
SELECT "Name", 5 "Note", SUM(CASE WHEN "Note" = 5 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"
UNION
SELECT "Name", 6 "Note", SUM(CASE WHEN "Note" = 6 THEN 1 ELSE 0 END) "Anzahl" FROM "Klasse" GROUP BY "Name", "Note"[/code]

Oder Du erstellst Dir eine extra Tabelle "Noten", in der Du einfach nur alle möglichen Noten eingibst. Eine Abfrage wie
[code]SELECT
"Name",
"Note",
SUM(CASE WHEN "Noten"."Note" = "Klasse"."Note" THEN 1 ELSE 0 END) "Anzahl"
FROM
"Noten" JOIN "Klasse" ON TRUE
GROUP BY
"Name","Note"
ORDER BY
"Name","Note"[/code]
ergibt dann ebenfalls obige Darstellung. Hat den gewissen Charme, dass Du nicht alle verfügbaren Noten hart in der Abfrage verankern musst, sondern einfach nur in die "Noten" Tabelle eintragen brauchst und die Note wird ohne weitere Änderungen mitgezählt. Allerdings auf Kosten eines Cross-Joins, der bei einer größeren Anzahl Datensätzen zum absoluten No-Go wird.

Ich persönlich würde aus mehreren Gründen den Weg hier nehmen
[code]SELECT
"Name",
SUM(CASE WHEN "Note" = 1 THEN 1 ELSE 0 END) "Anzahl 1er",
SUM(CASE WHEN "Note" = 2 THEN 1 ELSE 0 END) "Anzahl 2er",
SUM(CASE WHEN "Note" = 3 THEN 1 ELSE 0 END) "Anzahl 3er",
SUM(CASE WHEN "Note" = 4 THEN 1 ELSE 0 END) "Anzahl 4er",
SUM(CASE WHEN "Note" = 5 THEN 1 ELSE 0 END) "Anzahl 5er",
SUM(CASE WHEN "Note" = 6 THEN 1 ELSE 0 END) "Anzahl 6er",
AVG("Note"*1.0) "Durchschnitt"
FROM
"Klasse"
GROUP BY
"Name"[/code]

(Häkchen setzen bei "SQL-Kommando direkt ausführen" in der Symbolleiste erforderlich)

Hallo Phil,
was passiert,wenn Du COUNT(*) durch COUNT("Note") ersetzt ?