[mySQL] (te?) Geavenceerde query

[nihitsia]

Voor mijn huis ben ik bezig met een webbased systeem waarop we alle maaltijden bijhouden.

Als je gekookt hebt voer je de maaltijd in het systeem in en je kan je "stand" opvragen om te zien hoe je in de lijst staat.

Die stand wil niet helemaal lukken.

De database ziet er als volgt uit:
3 tabellen:
maaltijd(id, gerecht, datum, kok, kosten, externen)
eten(id, maaltijdid, bewonerid, aantal)
bewoner(id, naam)

Het meeste spreekt denk ik voor zich. "aantal" in de tabel "eten" is met hoeveel mensen een huisgenoot heeft meegegeten. Een vriendinnetje kan op de huislijst van haar vriendje meeëten dus dan telt hij voor 2 bij die maaltijd.


Nu wil ik het volgende aan de database vragen:
Bereken voor een bewoner (via id) hoeveel de som van de kosten per persoon maal het aantal meegegeten personen per maaltijd.

Om het duidelijker te maken met haakjes erbij:
Bereken met een bewoner id hoeveel de som van ((de kosten per persoon maal het aantal meegegeten personen) per maaltijd.)

Ik kan begrijpen als het wat vaag klinkt maar ik kan het niet helemaal goed verwoorden.

[Creepy]

En welke queries heb je zelf al bedacht? Wat lukte daar niet mee? Zie ook P&W FAQ - De "quickstart"

Voor zover ik je begrijp wil je gewoon een totaal van de kosten per bewoner uitrekenen? Met een innerjoin kan je toch voor elke row in eten de kosten bepalen en dat weer vermenigvuldigen met het aantal? Vervolgens per bewoner-id optellen en gaan?

[ Voor 51% gewijzigd door Creepy op 30-01-2006 23:08 ]

[NMe]

Met joins, GROUP BY en SUM moet je een heel eind kunnen komen?

[nihitsia]

Ik probeer het in stukken op te delen.

Ik ben nu bezig met "het aantal meegegeten personen"
Dat is dus het aantal meegeten huisgenoten (som van het aantaleters waar maaltijdid = "de maaltijd") + het aantal externen (select externen from maaltijd where id = "de maaltijd").

Maar ik kan in mySQL al niet dit doen:

code:

(SELECT sum(aantal) as totaal 
FROM eten where maaltijdid = 1) + 
(SELECT externen 
FROM maaltijd 
WHERE id = 1) 
as totaaleters

het gaat fout bij de +

[ACM]

Creepy schreef op maandag 30 januari 2006 @ 23:04:
Voor zover ik je begrijp wil je gewoon een totaal van de kosten per bewoner uitrekenen? Met een innerjoin kan je toch voor elke row in eten de kosten bepalen en dat weer vermenigvuldigen met het aantal? Vervolgens per bewoner-id optellen en gaan?

In tekst is ie inderdaad vrij makkelijk uit te leggen

Maar als query wordt het nog best lastig. Ik hoop trouwens dat er voor de maaltijdid+bewonerid een unique constraint bestaat, al dan niet impliciet? Want dan is het nog relatief eenvoudig te doen, anders zit je helemaal lastig omdat je dan ook nog eens moet corrigeren voor het aantal records per maaltijd van een enkele bewoner.

De bouwstenen die je dan nodig hebt zijn volgens mij de maaltijd-tabel voor de kosten, de eten-tabel voor de bewoner foreign key en nog een keer de eten-tabel maar dit maal voor de sum van het aantal per maaltijd. Met die drie kan je dan de kosten voor elke maaltijd waar de bewoner aan meedeed uitrekenen.
Als je mysql 4.1 of recenter gebruikt kan je die hele combinatie dan als "tabel genererende query" weer in de from-clause van een nieuwe aggregerende query stoppen en daarmee uiteindelijk je sum berekenen.

Even dat laatste nog met een triviaal voorbeeld toelichten:

SQL:

SELECT SUM(testveld1)
FROM
 (SELECT 1 as testveld1, 2 as testveld2
  UNION
  SELECT 3 as testveld1, 4 as testveld2) as virtueleTabel

Als het goed is komt er uit bovenstaande 4

[ACM]

nihitsia schreef op maandag 30 januari 2006 @ 23:11:
Ik probeer het in stukken op te delen.

Ik ben nu bezig met "het aantal meegegeten personen"
Dat is dus het aantal meegeten huisgenoten (som van het aantaleters waar maaltijdid = "de maaltijd") + het aantal externen (select externen from maaltijd where id = "de maaltijd").

Maar ik kan in mySQL al niet dit doen:

het gaat fout bij de +

Als dat niet mag omdat de query incorrect is kan je het vast beter zo aanpakken:

SELECT externen + SUM(aantal)
FROM
maaltijd JOIN eten ON eten.maaltijdid = maaltijd.id
WHERE maaltijd.id = 1
GROUP BY maaltijd.id

En als dat niet mag, zoiets:
SELECT
externen + totaal
FROM
(SELECT externen, SUM(aantal) as totaal
FROM
maaltijd JOIN eten ON eten.maaltijdid = maaltijd.id
WHERE maaltijd.id = 1
GROUP BY maaltijd.id) as test

Overigens zal je met temporary tables moeten werken als je mysql < 4.1 hebt... want die doen niet aan subqueries.

[ Voor 5% gewijzigd door ACM op 30-01-2006 23:22 ]

[EdwinG]

Als ik het probleem goed begrepen heb, kan ik in ieder geval wel het volgende doen:

Van persoon x gerekend opzoeken:

• Met welke maaltijden hij heeft meegedaan
• Hoeveel personen (per maaltijd) namens hem mee aten
• Totaal aantal eters van elke maaltijd
• Kosten van de maaltijd
• Aantal externen

Het is dan nodig om een alias voor een tabelnaam te kiezen. Ik weet niet precies vanaf welke mySQL versie dit ondersteund wordt.

SQL:

SELECT e2.maaltijdid AS maaltijd, (m.kosten / (sum(e1.aantal) + m.externen)) * e2.aantal AS 'Kosten voor bewoner', m.kosten AS 'totaal kosten', m.kok AS 'Bereid door'
  FROM maaltijd AS m
    INNER JOIN (eten AS e1)
      ON (m.id = e1.maaltijdid)
    INNER JOIN (eten AS e2)
      ON (e1.maaltijdid = e2.maaltijdid)
    INNER JOIN (bewoner AS b)
      ON (e2.bewonerid = b.id)
  WHERE b.id = '1'
  GROUP BY e1.maaltijdid

edit:
GROUP BY vergeten

edit:
Wijziging 2: Berekening ook in de query gegooid

[ Voor 35% gewijzigd door EdwinG op 31-01-2006 00:06 ]

[M-ThijZ]

Grappig, ik ben op dit moment met eenzelfde project bezig. Ben ook de kooklijst van mn studentenflat digitaal aan het maken, en ik denk dat ik het een en ander maar statisch in de database zet.