Strukturierte Ausgabe einer Tabelle SQL

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Die Tabelle Tage ist nur eine Hilfstabelle weil es nicht für jeden Tag einen Eintrag in der Tabelle Ausfallstunden gibt.

Tabelle Tage

Datum

03.09.2012
04.09.2012
05.09.2012
06.09.2012
07.09.2012

In der Tabelle Ausfallstunden sind die einzelnen Vorgänge gesammelt. Jeder Vorgang hat einen Datensatz.
Es können auch mehrere Vorgänge an einem Tag auftreten oder keiner.

Tabelle Ausfallstunden

Datum | Anzahl | Grund

04.09.2012 | 2 | 1
04.09.2012 | 3 | 2
06.09.2012 | 1 | 3
06.09.2012 | 3 | 3

Die Vorgänge mit dem selben Grund und am selben Tag sollen summiert werden.

Gewünschte Ausgabe:
Datum | Alle | Grund1 | Grund2 | Grund3

03.09.2012 | 0 | 0 | 0 | 0
04.09.2012 | 5 | 2 | 3 | 0
05.09.2012 | 0 | 0 | 0 | 0
06.09.2012 | 4 | 0 | 0 | 4
07.09.2012 | 0 | 0 | 0 | 0

Ich schaffe es bisher nur für einen AusfallGrund Die Abfrage zu schreiben:

SELECT Tage.Datum, SumAusfallstunden.Anzahl AS Grund1
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum
WHERE Ausfallstunden.
GROUP BY Tage.Datum

Ich habe auch schon mit sub-selects expermentiert was aber nicht zu gewünschten Erfolg führte:

SELECT Tage.Datum,
SUMSELECT Ausfallstunden.Anzahl FROM Ausfallstunden WHERE AS Grund1,
SUMSELECT Ausfallstunden.Anzahl FROM Ausfallstunden WHERE AS Grund2,
SUMSELECT Ausfallstunden.Anzahl FROM Ausfallstunden WHERE AS Grund3
FROM Tage
GROUP BY Tage.Datum

Für Eure Hilfe schonmal Dank im Vorraus
Martin

#2

Hallo,
das Group by muss auch den Grund enthalten, also
group by
Tage.Datum
,grund1
,grund2
,grund3
…

Wobei die Tabelle auch etwas „bescheiden“ aufgebaut ist.
Besser wäre es, für die Gründe eine eigene Tabelle aufzubauen.

Beispiel:
Tabelle Grund
ID_Grund Bez_Grund
1 Schlechtes Wetter
2 Lustlosigkeit
etc…

Und dann kann es in der Tabelle Ausfallstunden n-mal den Tag mit veschiedenen Gründen geben.
Beispiel:

ID_Ausfall Tag Stunden ID_Grund
1 04.09.2012 2 1
2 04.09.2012 1 2
etc…

Noch besser wäre es, mit ID’s zu arbeiten. Die Tabelle „Tage“ brauchst Du eh nicht.

Alles klar? :wink:

Ansonsten einfach noch einmal nachfragen…

Gruß
Flons

#3

Hallo Martin!

SELECT Tage.Datum,
SUMSELECT Ausfallstunden.Anzahl FROM Ausfallstunden WHERE AS
Grund1,
SUMSELECT Ausfallstunden.Anzahl FROM Ausfallstunden WHERE AS
Grund2,
SUMSELECT Ausfallstunden.Anzahl FROM Ausfallstunden WHERE AS
Grund3
FROM Tage
GROUP BY Tage.Datum

Ein einfache Möglichkeit der Abfrage ist durch Anpassung der Abfrage möglich:

Select Datum, Grund, Sum(Ausfallstunden)
From Ausfallstunden
Group By Datum, Grund

Im Ergebnis findest du je Tupel von Datum,Grund eine Ergebniszeile.

Soll je (unterschiedlichem) Grund eine neue Spalte im Ergebnis erscheinen, wirst du auf Kreuztabellenabfragen (Pivot Table) zurückgreifen müssen. Da diese Art der Abfragen nicht von jeder Datenbank unterstützt werden, solltest du mal im Handbuch deiner DB nachschauen. MS Access kann so etwas schon seit langem, andere DBMS können das erst in neueren Versionen. Da die Syntax abhängig von der DB ist, macht es wenig Sinn, jetzt auf ein Beispiel zu verweisen.

Im Zweifelsfall kannst du die Kreuztabellenabfrage aber von Hand nachbauen, wobei das Statement bei neuen Spalten angepasst werden muss:

SELECT Tage.Datum, Ausfallstunden.Anzahl, 0, 0
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum
WHERE Ausfallstunden.Grund = 1
GROUP BY Tage.Datum
UNION
SELECT Tage.Datum, 0, Ausfallstunden.Anzahl, 0
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum
WHERE Ausfallstunden.Grund = 2
GROUP BY Tage.Datum
UNION
SELECT Tage.Datum, 0, 0, Ausfallstunden.Anzahl
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum
WHERE Ausfallstunden.Grund = 3
GROUP BY Tage.Datum

=> Mit UNION können gleichartige Statements (gleiche Anzahl Spalten, gleiche Typen) vereinigt werden.

Von einem Subselect solltest du absehen. Bei größeren Datenmengen ist das ein totaler Performancekiller: 365 * 3 Typen = >1000 Unterabgfragen für einen Aufruf.

Gruß Markus

#4

Du hast mir leider nicht verraten, welche DB du nutzt. Aber versuche mal:

SELECT
 t.Datum,
 ( SELECT sum( a.Anzahl ) 
 FROM Ausfallstunden a 
 WHERE a.Datum = t.Datum ) AS Alle,
 ( SELECT sum( a.Anzahl ) 
 FROM Ausfallstunden a 
 WHERE a.grund = "1" AND a.Datum = t.Datum ) AS Grund1,
 ( SELECT sum( a.Anzahl ) 
 FROM Ausfallstunden a 
 WHERE a.grund = "2" AND a.Datum = t.Datum ) AS Grund2,
 ( SELECT sum( a.Anzahl ) 
 FROM Ausfallstunden a 
 WHERE a.grund = "3" AND a.Datum = t.Datum ) AS Grund3,
FROM Tage t

Alternativ:

SELECT
 Datum, Grund, SUM( Anzahl ) 
FROM Ausfallstunden
WHERE datum BETWEEN startTag AND endTag
GROUP BY Datum, Grund
ORDER BY Datum, Grund

Und dann die Tabelle in deiner Programlogik aufbauen. Dies würde dir auch die zweite Tabelle sparen.

Was performanter ist, hängt vom konkreten Fall ab. Auch ist es eine Frage, ob man mehr Last auf der Datenbank oder auf dem Anwendungsserver erzeugen möchte.

#5

Ich arbeite mit Access 2007.

Ich habe das umgesetzt aber er erzeugt immernoch für jeden Eintrag JEWEILS eine Zeile. Wenn kein Eintrag vorhanden ist erzeugt die Abfrage eine Zeile.
Es soll aber nur eine Zeile erzeugen:

SOLL (vereinfacht):

Datum|Gesamt|Krankheit| Sonstiges
-----+------+---------+------------
3.9. | 0 | 0 | 0
-----+------+---------+------------
4.9. | 2 | 1 | 1
-----+------+----------------------
5.9 | 5 | 3 | 2
-----------------------------------

IST (vereinfacht):

Datum|Gesamt|Krankheit| Sonstiges
-----+------+---------+------------
3.9. | 0 | 0 | 0
-----+------+---------+------------
4.9. | 0 | 1 | 0
-----+------+----------------------
4.9. | 0 | 0 | 1
-----+------+----------------------
4.9. | 2 | 0 | 0
-----+------+----------------------
5.9. | 0 | 3 | 0
-----+------+----------------------
5.9. | 0 | 0 | 2
-----+------+----------------------
5.9 | 5 | 0 | 0
-----------------------------------

Mein aktueller Code:

SELECT Tage.Datum, SUM(Ausfallstunden.Anzahl) AS Gesamt, 
0 AS Krankheit,
0 AS Beurlaubung,
0 AS Ehrenamt,
0 AS Fortbildung,
0 AS Dienstbesprechung,
0 AS Exkursion,
0 AS Praktikumsbetreuung,
0 AS Klassenfahrt,
0 AS SCHILF,
0 AS Prüfung,
0 AS Konferenz,
0 AS Elternsprechtag,
0 AS Sonstige
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, SUM(Ausfallstunden.Anzahl), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 1
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, SUM(Ausfallstunden.Anzahl), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 2
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, 0, SUM(Ausfallstunden.Anzahl), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 3
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, 0, 0, SUM(Ausfallstunden.Anzahl), 0, 0, 0, 0, 0, 0, 0, 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 4
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, 0, 0, 0, SUM(Ausfallstunden.Anzahl), 0, 0, 0, 0, 0, 0, 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 5
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, 0, 0, 0, 0, SUM(Ausfallstunden.Anzahl), 0, 0, 0, 0, 0, 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 6
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, 0, 0, 0, 0, 0, SUM(Ausfallstunden.Anzahl), 0, 0, 0, 0, 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 7
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, 0, 0, 0, 0, 0, 0, SUM(Ausfallstunden.Anzahl), 0, 0, 0, 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 8
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, 0, 0, 0, 0, 0, 0, 0, SUM(Ausfallstunden.Anzahl), 0, 0, 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 9
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, SUM(Ausfallstunden.Anzahl), 0, 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 10
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, SUM(Ausfallstunden.Anzahl), 0, 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 11
GROUP BY Tage.Datum

UNION
SELECT Tage.Datum, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, SUM(Ausfallstunden.Anzahl), 0 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 12
GROUP BY Tage.Datum

UNION SELECT Tage.Datum, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, SUM(Ausfallstunden.Anzahl) 
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum = Ausfallstunden.Datum 
WHERE Ausfallstunden.Gründe = 13
GROUP BY Tage.Datum;

Vielen Dank nochmal für Deine schnelle Hilfe!

#6

Bei der zweiten Lösung war die Ausgabe auch so gedacht. Über zwei Schleifen würde man dieses dann auslesen, die Zeilen zusammen bauen und dann ausgeben.

In Access ist die erste Lösung für dich vermutlich dann die bessere. Die Abfrage wird halt nur etwas länger. Habe auch noch einen kleinen Fehler gefunden (ein Komma) und wenn „Grund“ als „Zahl“ angelegt ist, dann müssen auch die doppelten Anführungszeichen weg.

SELECT
 t.Datum,
 ( SELECT sum( a.Anzahl ) FROM Ausfallstunden a WHERE a.Datum = t.Datum ) AS Alle,
 ( SELECT sum( a.Anzahl ) FROM Ausfallstunden a WHERE a.grund = 1 AND a.Datum = t.Datum ) AS Grund1,
 ( SELECT sum( a.Anzahl ) FROM Ausfallstunden a WHERE a.grund = 2 AND a.Datum = t.Datum ) AS Grund2,
 ( SELECT sum( a.Anzahl ) FROM Ausfallstunden a WHERE a.grund = 3 AND a.Datum = t.Datum ) AS Grund3
FROM Tage t
#7

Hallo,

bitte entschuldige die etwas verspätete Antwort.
Leider ging Deine Anfrage im Wust vieler Mails etwas unter.
Aber da ja bereits einige brauchbare Lösungen vorliegen muss ich ja nichts wiederholen.

Gruß
Erhard

Tabelle Tage

Datum

03.09.2012
04.09.2012
05.09.2012
06.09.2012
07.09.2012

In der Tabelle Ausfallstunden sind die einzelnen Vorgänge
gesammelt. Jeder Vorgang hat einen Datensatz.
Es können auch mehrere Vorgänge an einem Tag auftreten oder
keiner.

Tabelle Ausfallstunden

Datum | Anzahl | Grund

04.09.2012 | 2 | 1
04.09.2012 | 3 | 2
06.09.2012 | 1 | 3
06.09.2012 | 3 | 3

Die Vorgänge mit dem selben Grund und am selben Tag sollen
summiert werden.

Gewünschte Ausgabe:
Datum | Alle | Grund1 | Grund2 | Grund3

03.09.2012 | 0 | 0 | 0 | 0
04.09.2012 | 5 | 2 | 3 | 0
05.09.2012 | 0 | 0 | 0 | 0
06.09.2012 | 4 | 0 | 0 | 4
07.09.2012 | 0 | 0 | 0 | 0

Ich schaffe es bisher nur für einen AusfallGrund Die Abfrage
zu schreiben:

SELECT Tage.Datum, SumAusfallstunden.Anzahl AS Grund1
FROM Tage LEFT JOIN Ausfallstunden ON Tage.Datum =
Ausfallstunden.Datum
WHERE Ausfallstunden.
GROUP BY Tage.Datum

Ich habe auch schon mit sub-selects expermentiert was aber
nicht zu gewünschten Erfolg führte:

SELECT Tage.Datum,
SUMSELECT Ausfallstunden.Anzahl FROM Ausfallstunden WHERE AS
Grund1,
SUMSELECT Ausfallstunden.Anzahl FROM Ausfallstunden WHERE AS
Grund2,
SUMSELECT Ausfallstunden.Anzahl FROM Ausfallstunden WHERE AS
Grund3
FROM Tage
GROUP BY Tage.Datum

Für Eure Hilfe schonmal Dank im Vorraus
Martin

#8

Hallo
Wenn diese Aussage stimmt
Die Vorgänge mit dem selben Grund und am selben Tag sollen summiert werden.

Dann muss der Grund bei
group by
berücksichtigt werden.

Ich persönlich schreibe keine left join da ich eine Oracle-DB benütze.
Ist der PK-Key Datum, Grund ?

Was ist Alle in
Gewünschte Ausgabe: ?

Mit Oracle würde ich die Pivot-Funktion nützen.
Hoffe Dir Hilft das.