Summenbildung aus einer Summe

[croettgers]

Guten Tag,
ich bin jetzt schon einige Zeit am überlegen, wie ich eine Abfrage in unsere Zeiterfassung erstellen kann.

Folgende Informationen:

Es gibt die Tabelle "Zeiten" mit den Feldern "Personalnummer", "Lohnart" und "Dauer".

Als weitere Tabelle habe ich die Tabelle "lohnart" als Lohnartenbeschreibung, wo das Feld "Kostengruppe" herangezogen werden soll. Die Lohnarten aus der Tabelle Zeiten werden durch die Tabelle "Lohnart" den Kostengruppen zugeordnet.

Die Tabelle Zeiten gibt mir die diversen Buchungssätze mit Personalnummer, Lohnart und Dauer an.

Folgende Select-Anweisung gibt mir ein erstes Ergebnis

***
select
zeiten.pnr as p,
lohnart.kostengruppe as k,
sum(zeiten.dauer) as s

from
test.zeiten, test.lohnart

where
zeiten.lohnart=lohnart.lohnartnr

group by
zeiten.pnr, lohnart.kostengruppe

***

Jetzt habe ich die Ausgabe nach Kostengruppen zusammengefasst.

Als Beispiel

p k s
10 1 15
10 2 35
10 3 50
20 1 5
20 2 250
30 1 20
30 3 400

Für eine Übergabe an ein externes Programm, benötige ich die prozentuale Aufteilung, d.h. eine 4. Spalte, wo der prozentuale Anteil an den Gesamtminuten des Mitarbeiters angezeigt wird.

Als Beispiel
Personalnummer 10 hat auf 3 Kostengruppen gearbeitet, mit insgesamt 100 Min = 100 %. Auf die Kostengruppe 1 entfällt 15 %, auf 2 35 % und auf 3 50 %.

Somit hätte ich eine 4. Spalte mit den Prozentangaben.

Ich habe es mit einer verschachtelten Selectanweisung versucht, bekomme aber kein Ergebnis.

Für einen Mitarbeiter bekomme ich es hin, z.B.

*****

select
zeiten.pnr as p,
lohnart.kostengruppe as k,
sum(zeiten.dauer) as s,

(
select sum(zeiten.dauer)
from zeiten
where pnr=10
)
as zeitsumme

from
test.zeiten, test.lohnart

where
zeiten.lohnart=lohnart.lohnartnr
and pnr=10

group by
zeiten.pnr, lohnart.kostengruppe

***

Aber hier ist explizit ein Mitarbeiter gewählt. Wir haben aber 40. In der Beispieltabelle also noch PNR 20 und 30.

Hat jemand einen Lösungsvorschlag?

Gruss
Carsten

[dibo33]

Du musst dein Subselect entspr. anpassen.

select sum(zeiten.dauer)
from zeiten
where pnr=p

Edit: wäre mögl. das der Alias nicht greift

select sum(z.dauer)
from zeiten z
where zeiten.pnr=z.pnr

[croettgers]

Guten Morgen,

vielen Dank für die schnelle Hilfe. Klappt auch mit der ersten Version. Nun habe ich meine Zeitsumme als Basis für den Mitarbeiter.

Jetzt muss die 5. Spalte das Ergebnis in % enthalten, also rechnerisch (s*100)/zeitsumme. Kann man jetzt noch einen 3. Subselect einbauen? Und wenn, dann wie?

Gruss
Carsten

[dibo33]

Könnte man, wäre aber unnötig.

Als erstes solltest du von der alten Schreibweise für Joins absehen und nur "echte" Joins nutzen (also ausschreiben), dann sieht deine erste Abfrage so aus

SELECT z.pnr p, l.kostengruppe k, SUM(z.dauer) s
FROM zeiten z
JOIN lohnart l
    ON z.lohnart = l.lohnartnr
GROUP BY p, k

Darauf aufbauend setzen wir noch ein Self-Join auf die Zeiten-Tabelle und erhalten somit die Summe (auch resourcen schonender)

SELECT z.pnr p, l.kostengruppe k, SUM(z.dauer) s, sd
FROM zeiten z
JOIN lohnart l
    ON z.lohnart = l.lohnartnr
JOIN (
    SELECT pnr, SUM(dauer) sd
    FROM zeiten
    GROUP BY pnr
) t1
    USING (pnr)
GROUP BY p, k, sd

Damit ist es ein leichtes noch die Prozent zu ermitteln

SELECT z.pnr p, l.kostengruppe k, SUM(z.dauer) s, sd, ROUND((SUM(z.dauer)*100)/sd) proz
FROM zeiten z
JOIN lohnart l
    ON z.lohnart = l.lohnartnr
JOIN (
    SELECT pnr, SUM(dauer) sd
    FROM zeiten
    GROUP BY pnr
) t1
    USING (pnr)
GROUP BY p, k, sd

[croettgers]

hallo dibo 33,

vielen dank für die unterstützung. ich habe mich schonmal intensiv mit sql auseinandergesetzt, war aber schon lange her. jetzt versuche ich es einfach nochmal

könntest du mir die abfrage bitte erläutern:

a) die verkürzte schreibweise mit den tabellen alias sind mir verständlich

b) anstatt die verknüpfung über 'where' zu machen, wird es über einen join gemacht. wo ist der unterschied?

c) der 2. join macht eine abfrage aus der 1. abfrage und gibt die variable sd aus, die in der hauptabfrage eingesetzt wird. die variable t1 ist nur ein lückenfüller?

d) was bedeutet using (pnr)

vielen dank nochmals

gruss
carsten

[Falk Prüfer]

Hallo,

ich bin zwar nicht dibo33, aber ich versuchs trotzdem mal:

b) anstatt die verknüpfung über 'where' zu machen, wird es über einen join gemacht. wo ist der unterschied?

Rein technisch ist es IMMER ein Join, auch wenn die Bedingung in der Where-klausel formuliert wird. Die explizite Formulierung eines Join hat den Vorteil, dass sie leichter lesbar ist und eine saubere Trennung zwischen der eigentlichen Join-Bedingung und evtl. zusätzlich einschränckenden Where-Bedingungen erfolgt. Die implizite Join-Definition über die Where-Klausel gestattet zudem nur den INNER JOIN. Ein OUTER JOIN muß immer explizit formuliert werden.

c) der 2. join macht eine abfrage aus der 1. abfrage und gibt die variable sd aus, die in der hauptabfrage eingesetzt wird. die variable t1 ist nur ein lückenfüller?

Nein, der zweite Join verwended statt einer Tabelle eine s.g. Subquery, also eine Unterabfrage - manchmal auch als Inline-View bezeichnet. Es wird auch keine "Variable" ausgegeben. Das Aggregat (SUM(dauer)) erhält über eine Aliasdefinition den Namen "sd". Auch t1 ist keine "Variable". t1 ist der Alias der Subquery. Bei der Verwendung von Subqueries in MySQL ist dieser Alias zwingend notwendig. (Tabellen benötigen dagegen nicht zwingend einen Alias)
Wenn dibo33 konsequent gewesen wäre und die Aliase durchgängig benutzt hätte, dann würde der Select-Teil so aussehen:
[highlight=sql]
SELECT z.pnr p, l.kostengruppe k, SUM(z.dauer) s, t1.sd, ROUND((SUM(z.dauer)*100)/t1.sd) proz
...
[/highlight]

d) was bedeutet using (pnr)

USING (pnr) weist den JOIN an, aus beiden gejointen Tabellen (wobei die eine Tabelle hier eine Subquery ist) jeweils das Feld pnr für die Join-Bedingung zu verwenden. Ausformuliert würde es also so aussehen:
[highlight=sql]
...t1
ON t1.pnr = z.pnr
...
[/highlight]

Gruß Falk

[croettgers]

Hallo Falk,

vielen Dank für die Ausführungen. Kennst Du Literatur, die gut verständlich auf das Thema SQL eingeht? SQL für Dummies etc. ist mir zu oberflächlich.

Gruss
Carsten

[Falk Prüfer]

Hallo Carsten,

Eine gute Einführung ist z.B.: Wikibooks - Einführung in SQL.

Gruß Falk