Wie kann ich summe, join und find_in_set vereinen?

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

Liebe/-r Experte/-in,

ich bin mal wieder in einer Sackgasse. Und zwar möchte ich für einen Verein sowas wie eine Aufwandsentschädigung realisieren.
Da gibt es ua (vereinfacht) eine tabelle arbeitseinsaetze mit id,km,taetigkeiten,material_id
In der Tabelle Material gibt es id, kosten,beschreibung,mg_id

Nun hat Klaus Meier für den Verein 3 Stunden investiert, ist in die Stadt 12km gefahren und hat Papier und Druckerlartouschen gekauft und neue Bälle.

In Arbeitseinsaetze steht dann
id|km|taetigkeiten|material_id|zeit|
1|24|Buchführung 2010|1,2,3|3|

In material steht dann:
id|kosten|beschreibung|einsatz_id|
1|25|Druckertinte|1|
2|3|papier|1|
3|17|Bälle|1|

Also hat Klaus Meier 3 Stunden Buchführung gemacht und für den Verein Papier, Bälle und Druckerkartouschen gekauft.

Anzeigen soll er mir nun die Summe der Materialkosten, Die Materialien,die Zeit und die km.

Nehme ich ein simples group by arbeitseinsaetze.id macht er mir aus den 3 Stunden 6 Stunden. Lasse ich es weg, macht er mir statt der Materialsumme nur den letzten wert.

Es können zB aber auch an 2 Tagen was gemacht worden sein, zB Zaun streichen und die brauchen kein Material oder die Leute sind zwei Mal gekommen.

SELECT
sum(quittungen.preis)
+sum(arbeitseinsatz.km*0.3))
as gesamtkosten,
sum(arbeitseinsatz.zeit) as gesamtzeit.
arbeitseinsatz.einsatz_id from arbeitsinsaetze,mitglieder,quittungen where find_in_set(auittungen.id,arbeitseinsatz.quittung) and arbeitseinsaetze.id=810 group by arbeitseinsatz.id

Was raus kommen soll, ist eine Liste der Arbeitseinsätze, wo ich sehen kann, wer wie viel geleistet hat und wer was erstattet bekommt.

Klaus Meier | 3 Stunden| Buchführung |Papier, Tinte, Bälle | 36 € | 24km |Summe: 44€
Peter Schnidt | 6Stunden | zaun gestrichen | kein Material| 0 | 0 | Summe. 0 €

Eine unschöne Möglichkeit wäre jedesmal eine Zeile für jeden Part zu erzeugen:
1 Arbeitseinsatz à 4 Stunden an zwei Tagen braucht 3 Teile. Dann hätte ich alleine dafür 5 Einträge mit überwiegend leeren Feldern.

id|km|taetigkeiten|material_id|zeit|Einsatz_id|
1|24|Zaun streichen |0|4|15
2|0|Zaun streichen |0|4|15
3|0|Zaun streichen |1|0|15
4|0|Zaun streichen |2|0|15
5|0|Zaun streichen |3|0|15

Eine andere Variante wäre via php rechnen zu lassen.
Aber das müsste doch auch mit mysql gehen, oder?
Danke für Deine Hilfe!
Oliver

#2

Hallo Oliver, eehrlich gesagt, blick ich bei deinen Tabellen nicht richtig durch. Mit group by kommst du hier nicht weiter. Das müßte dann group by taetigkeit sein, dann eher mit join Abfragen.
Was spricht denn dagegen zusatzlich noch mit php zu rechnen.

Gruß Monika

#3

Hallo Monika,

danke für Deine superschnelle Antwort.

ich versuchs mal anders zu erklären.

Also:
Eine Tabelle heißt Arbeitseinsätze:
Ein Arbeitseinsatz kann aus Material, Zeit und Fahrtkosten bestehen. Ein Arbeitseinsatz kann aus Teileinsätzen bestehen.

Also als Beispiel. Vereinsheim wurde von Klaus, Fritz und Melanie gestrichen. Melanie hat alle abgeholt und ist danach in die Stadt gefahren und hat Farbe und nen Pinsel und was weiss ich Spachtelmasse gekauft.

Für den Teileinsatz 1 gehen also 3x Material drauf +km Geld und 3 Stunden Arbeit.
Beim 2. Teileinsatz wir die Hütte ein 2. Mal gestrichen. Weiter wird aber kein Material gebraucht und Fahrtkosten entstehen auch nur 1x.

Also hätte ich hierdurch zwei Zeilen in der Tabelle:
id|taetigkeit|mitglied_ids|material_ids|km|zeit
1|Vereinsheim gestrichen|1,2,3|3,4,5|23|3
2|Vereinsheim gestrichen|1,2|0|0|1

Also haben am ersten Tag 3 Leute 3 Stunden gestrichen, Geld für Farbe etc. ausgelegt und sind 23 km gefahren.
Beim 2. Durchgang sind nur noch 2 Leute dagewesen und haben nur 1 Stunde gestrichen. Die haben keine Fahrtkosten und kein Material gebraucht

Also Ausgabe möchte ich nun haben:
Gesamtzeit: 4 Stunden,Material für 45 € und 29 km.

#4

Moin Oliver Eikel,

ich bin mal wieder in einer Sackgasse. Und zwar möchte ich für
einen Verein sowas wie eine Aufwandsentschädigung realisieren.
Da gibt es ua (vereinfacht) eine tabelle arbeitseinsaetze mit
id,km,taetigkeiten,material_id

Dinge wie „_id“ haben in einem MySQL Spaltennamen nix zu suchen. Auch wenns dir bei solch einfachen Dingen wie dem hier nicht im Weg ist wirst du dich weiter entwickeln. Dann kommt der Stolperstein. Gewöhn es dir besser gleich ab.

In der Tabelle Material gibt es id, kosten,beschreibung,mg_id

Nun hat Klaus Meier für den Verein 3 Stunden investiert, ist
in die Stadt 12km gefahren und hat Papier und
Druckerlartouschen gekauft und neue Bälle.

In Arbeitseinsaetze steht dann
id|km|taetigkeiten|material_id|zeit|
1|24|Buchführung 2010|1,2,3|3|

Ähmm… und wo ist der Bezug zu Klaus Meier?

In material steht dann:
id|kosten|beschreibung|einsatz_id|
1|25|Druckertinte|1|
2|3|papier|1|
3|17|Bälle|1|

Also hat Klaus Meier 3 Stunden Buchführung gemacht und für den
Verein Papier, Bälle und Druckerkartouschen gekauft.

Es gibt also noch eine Tabelle in der die Tätigkeiten katalogisiert sind. Aus der Erfahrung heraus rate ich dir: Schmeiss wech und erfasse die Tätigkeiten mit Typ „text“. Grund: Die Tätigkeiten heute zu katalogisieren funktioniert, weil du sie alle kennst. In 2, 5 oder 10 Jahren kann es Tätigkeiten geben, die du nicht kennst.

Anzeigen soll er mir nun die Summe der Materialkosten, Die
Materialien,die Zeit und die km.

Nehme ich ein simples group by arbeitseinsaetze.id macht er
mir aus den 3 Stunden 6 Stunden. Lasse ich es weg, macht er
mir statt der Materialsumme nur den letzten wert.

Logisch. Ne Gruppe sind mindestens 2. Wenn er nur einen Datensatz findet wird der kopiert.

Es können zB aber auch an 2 Tagen was gemacht worden sein, zB
Zaun streichen und die brauchen kein Material oder die Leute
sind zwei Mal gekommen.

Dann erfass das Datum der Ausführung mit und erzeuge für jede Tätigkeit eines Arbeiters einen neuen Eintrag.

SELECT
sum(quittungen.preis)
+sum(arbeitseinsatz.km*0.3))
as gesamtkosten,
sum(arbeitseinsatz.zeit) as gesamtzeit.
arbeitseinsatz.einsatz_id from
arbeitsinsaetze,mitglieder,quittungen where
find_in_set(auittungen.id,arbeitseinsatz.quittung) and
arbeitseinsaetze.id=810 group by arbeitseinsatz.id

Sry. Aber das ist absoluter Oberhammerquatsch.
Der Syntax ist falsch und wenn du das in 6 Monaten editieren sollst, wünsch ich dir viel Spass beim Hirn entknoten.

Was raus kommen soll, ist eine Liste der Arbeitseinsätze, wo
ich sehen kann, wer wie viel geleistet hat und wer was
erstattet bekommt.

Klaus Meier | 3 Stunden| Buchführung |Papier, Tinte, Bälle |
36 € | 24km |Summe: 44€
Peter Schnidt | 6Stunden | zaun gestrichen | kein Material| 0
| 0 | Summe. 0 €

Eine unschöne Möglichkeit wäre jedesmal eine Zeile für jeden
Part zu erzeugen:
1 Arbeitseinsatz à 4 Stunden an zwei Tagen braucht 3 Teile.
Dann hätte ich alleine dafür 5 Einträge mit überwiegend leeren
Feldern.

Schönheit liegt im Auge des Betrachters (J. W. v. Goethe).
Computer und Datenbanken haben keine Augen und kennen keine Ästhetik. Sie kennen Effizienz und Kosten.

Leere Felder wiegen und kosten nichts.

id|km|taetigkeiten|material_id|zeit|Einsatz_id|
1|24|Zaun streichen |0|4|15
2|0|Zaun streichen |0|4|15
3|0|Zaun streichen |1|0|15
4|0|Zaun streichen |2|0|15
5|0|Zaun streichen |3|0|15

Eine andere Variante wäre via php rechnen zu lassen.

Jetzt wird langsam ein Schuh draus.

Aber das müsste doch auch mit mysql gehen, oder?

Natürlich. Mit dem richtigen Datenbanklayout (nicht deines) und wenn alle jemals auftretenden Geschehnisse heute schon bekannt sind (nicht möglich).

Also:

  • Mach ne Tabelle ‚mitarbeiter‘

  • Darin gibt es ‚id‘ (index, auto_increment, bigint 20) und ‚arbeitername‘ (text)

  • meinetwegen noch ‚vorname‘ (text).

  • ne Tabelle ‚taetigkeiten‘

  • darin gibt es ‚id‘ (bigint 20), ‚km‘ (bigint 20), ‚taetigkeit‘ (text), ‚datum‘ (date), ‚zeit‘ (bigint 20).

Dann kannst du mit ein paar einfachen Anweisungen und Statements (SELECT * FROM taetigkeiten WHERE id = 1)
1 ist Klaus Meier. Und schwupps hast du alle Tätigkeiten von Klaus Meier aufm Display. Oder im php array. Summieren (oder was auch immer) machst dann in php.
Wenn du fürs Jahr 2010 und Klaus Meier filtern willst, schreibst einfach

SELECT * FROM taetigkeiten WHERE id = 1 AND date BETWEEN 2010-01-01 AND 2010-12-31

ins Statement.

Nimm die Datenbank ausschliesslich zum Aufbewahren der Daten. Zum Rechnen die Scriptsprache. Du benutzt mit php die mächtigste Scriptsprache. Im rechnen wesentlich flinker als MySQL.

Und noch ein Tipp für die Zukunft: Vereinfache.

widecrypt

#5

Hallo,

ich würde das in php berechnen.
Ansonsten vielleicht so:

SELECT m.name, sum(e.km), sum(e.zeit),

(SELECT sum(kosten) FROM material
GROUP BY einsatz_id
HAVING einsatz_id = e.id) AS materialkosten

FROM mitglied m LEFT JOIN einsatz e
ON e.mitglied_id = m.id
GROUP BY name

Gruß

fehlalarm

#6

Hallo,

leider kann ich bei diesem Problem nicht weiterhelfen.

LG

Ajo

#7

Hi,
ein fettes Dankeschön! Subquery heisst das Zauberrwort. Damit gehts!

#8

Hallo Oliver,

ich habe immer vermieden komplexere Abfragen mit mySQL zu machen. Ich löse das mit PHP. Entweder habe ich Probleme komplexe Abfragen zu realisieren. Aber ich habe das Gefühl ich kann das mit PHP besser lösen. Mir scheint es zumindest klarer und besser zu testen.
Also eine Antwort wie Du das mit mySQL erledigst kann ich Dir so nicht geben.

Ich habe nur den Eindruck dass Deine Datenbank mit einsatz_id in material rekursiv ist, da alle Materialeinträge in einsatz vorhanden sind. Also erstmal die Einsätze abfragen und in einer Schleife sehen ob Material dafür gebraucht wurde. Dann muss es zu lösen sein. Es sind doch wohl keine Einträge für Material ohne Einsätze??? Dann hättest Du vielleicht Schwierigkeiten.

Vielleicht hilft der Hinweis. Dann viel Erfolg.

Werner Esdohr

#9

Hi,
danke für Deinen ausführlichen Hinweis. Hm alles von php machen zu lassen ist nicht immer gut. Bei so kleinen Projekten ist es völlig egal.
Aber wenn die Projekte größer sind/werden, so dass es einen eigenen mysql-Server und eignen php-Server gibt, finde ich es besser etwas auf das load balancing zu achten. Sonst wartet der mysql-Server nachher auf den php-Server.

Also geht es mir hierbei auch um den Lerneffekt und natürlich der persönliche Ehrgeiz.

Und EInsätze wo einer nur Material gekauft hat sind einfach mit Zeit = 0 erfasst.

Schönes WE
Oliver

#10

Hallo Oliver,

erstmalmuss ich mich für die lange Antwortzeit entschuldigen. Als ich das erste mal die Anfrage gesehen habe, habe ich nur an „O, Gott! Schon wieder ein missratenes Datenmodell!“ gedacht. Und auch beim zweiten Nachdenken kann ich Dir nur raten das Datenmodell zu mindestens soweit zu erweitern, dass die Datenbank die Unit (KM, €, etc) für den Resourceneinsatz kennt. Als weiters würde ich auf jeden Fall eine Überarbeitung der Listen aufzählung für die genutzen Resourcen empfehlen.

MfG Georg V.

#11

Hallo,

leider kann ich dir bei der Frage nicht helfen.

LG
Manfred

#12

So, jetzt ist er wieder fit und kann auch antworten, falls sich das nicht erledigt haben sollte.
Also erstmal bin ich als freund der Normalisierung mal gar nicht mit der Tabellenstruktur einverstaden. Ich würde daraus 3 Tabellen machen - Derzeit 2, damit keine Redundanzen auftreten und dann lässt sich das auch alles viel einfacher und ohne großen Aufwand rechnen. Du machst es Dir selber zu kompliziert.

#13

Hi Georg,
ich habe es nun soweit gelöst. Das Zauberwort ist Subquery.
Hm, nun ja missratenes Datenbankdesign habe ich nun schon mehrfach gehört.

Vielleicht ist es wirklich Käse oder aber es liegt daran, dass ich nur Auszüge beschrieben hatte, damit es nicht zu kompliziert wird…

Einfach jedesmal eine neue Zeile und in jeder Zeile 85% „0“ drin stehen, wenn zB nur gearbeitet worden ist oder nur Material gebraucht wurde oder nur Fahrtkosten entstanden sind, müsste der Rest „0“ sein.

#14

Hallo,

da ich Ihre email gerade erst lese, leider im Moment allerdings nicht wirklich Zeit habe kann ich Ihnen im Moment nicht weiterhelfen ausser Sie auf die Seite http://dev.mysql.com/doc/refman/5.1/de/select.html und besonders die Punkte JOIN, GROUP BY und HAVING näherbringen.

bis denn denn

Daniel

#15

danke für deine antwort
subselect ist das zauberwort!

#16

hallo eikel,

sorry, kann dir im moment leider nicht weiterhelfen.

cu
harald

#17

Danke für Deine Antwort!
subquery ist das Zauberwort!