SUM joins 3 tabellen

[Willie-wortel]

Zo zien mijn tabellen eruit. Nu wil ik per transactie nummer kunnen zien wat de totaalprijs is geweest. Dus de som van de product_price + productoptions_price......

code:

SELECT transaction_number, date, time, employee, paymode, SUM( product_price) AS som_prijs, SUM(productoptions_price) AS som_prijsoptions
FROM administration_transactions
LEFT JOIN administration_products ON transaction_number = transaction_number
LEFT JOIN administration_productoptions ON product_id = productoptions_id
GROUP BY transaction_number

Dit werkt niet aangezien hij de adminsitration_products tabel vaker meerekent.....:S

[ Voor 18% gewijzigd door Willie-wortel op 28-03-2008 21:21 ]

[The Fox NL]

Ik heb geen databaseserver draaien om het uit te testen, maar dit is wat ik gefabriceerd heb:

SQL:

select y.transaction_number, ats.datetime, ats.employee, ats.paymode, (y.product_price + y.options_price) as total_price
from (
  select x.transaction_number, sum(x.product_price), sum(x.options_price) 
  from (
    select ap.transaction_number, ap.product_price, sum(apo.productoptions_price) as options_price
    from administration_products
    left join administration_productoptions apo on ap.product_id = apo.product_id
  ) x 
  group by x.transaction_number
) y
join administration_transactions ats on ats.transaction_number = y.transaction_number

[Willie-wortel]

bedankt voor je reactie, misschien vergeten erbij te zeggen, het gaat om MySQL
(weet niet of je zo meerdere select statements kan uitvoeren?)

[P.O. Box]

hmm... waarom niet:

SQL:

SELECT transaction_number, date, time, employee, paymode, product_price + SUM( productoptions_price) AS som_prijs
FROM administration_transactions
LEFT JOIN administration_products ON transaction_number = transaction_number
LEFT JOIN administration_productoptions ON product_id = productoptions_id
GROUP BY transaction_number, date, time, employee, paymode, product_price 

[ Voor 5% gewijzigd door P.O. Box op 28-03-2008 22:37 ]

[The Fox NL]

Geen flauw idee, ik ben op mijn werk Firebird gewoon, en daar kan ik deze kunstjes wel uithalen. Ik weet niet of MySQL views ondersteunt, maar als mijn statement niet werkt vanwege de select from select constructie, dan zou je het met Views kunnen proberen.

[Willie-wortel]

@: Edwardvb:

een transactie kan meerdere producten hebben waarvan bepaalden producten ook opties hebben (bijvoorbeeld: gewicht = +€ 2,00). Jouw query gaat uit van 1 product als ik het zo zie?

[Verwijderd]

Edwardvb schreef op vrijdag 28 maart 2008 @ 22:37:
hmm... waarom niet:

SQL:

SELECT transaction_number, date, time, employee, paymode, product_price + SUM( productoptions_price) AS som_prijs
FROM administration_transactions
LEFT JOIN administration_products ON transaction_number = transaction_number
LEFT JOIN administration_productoptions ON product_id = productoptions_id
GROUP BY transaction_number, date, time, employee, paymode, product_price 

Kan misschien in MySQL, maar 't is geen valid SQL: product_price zit in je GROUP BY, maar niet in je SELECT, en product_price + SUM( productoptions_price) AS som_prijs is geen geaggregeerde kolom, en mag dus niet in je SELECT voorkomen.

Bovendien, wat is 't nut van SUM( productoptions_price) als je per record maar 1 record uit administration_productoptions joint?

Een query met subselects zoals hierboven genoemd is de enige goede mogelijkheid, want met een LEFT OUTER JOIN kun je doodgewoon niet meer dan 1 record uit de gejoinde tabel intrekken.

[P.O. Box]

Willie-wortel schreef op vrijdag 28 maart 2008 @ 22:50:
@: Edwardvb:

een transactie kan meerdere producten hebben waarvan bepaalden producten ook opties hebben (bijvoorbeeld: gewicht = +€ 2,00). Jouw query gaat uit van 1 product als ik het zo zie?

dat klopt inderdaad... ik had niet goed geredeneerd....

@hierboven: je hebt helemaal gelijk... ik had te snel gereageerd

[The Fox NL]

Nu ik zo kijk naar mijn eerder gegeven query ben ik niet helemaal zeker van mijn binnenste select statement.

Ik denk dat deze query toch beter is:

SQL:

select y.transaction_number, ats.datetime, ats.employee, ats.paymode, (y.product_price + y.options_price) as total_price
from (
  select x.transaction_number, sum(x.product_price), sum(x.options_price) 
  from (
    select ap.transaction_number, ap.product_price, x.options_price
    from (
      select apo.product_id, sum(apo.options_price)
      from administration_productoptions apo
      group by apo.product_id
    ) z
    join administration_products ap on ap.product_id = z.product_id
  ) x 
  group by x.transaction_number
) y
join administration_transactions ats on ats.transaction_number = y.transaction_number

Wat trouwens de reden is van al mijn subquery's:
Bij de originele query van de TS wordt eerst de data opgehaald voordat er een group by wordt uitgevoerd:

transn	product	price	optpric
1	1	10	
2	1	10
2	2	10	1
2	2	10	2

Je ziet al dat de prijs van product 2 tweemaal voorkomt in de tabel, na een groepering op transactienummer en een sum op prijs en optieprijs krijg je de volgende data:

transn	price	optpric
1	10	
2	30	3

Omdat de prijs van produkt 2 nu tweemaal is geteld heb je 10 euro teveel.

Het is dus zaak de gegevens voor het groeperen op transactienummer zo te krijgen dat de gegevens maar 1 record per product bevatten met daarin de prijs van het product en de som van de opties.
Daar zorgt het stuk "select ap.transaction_number, ap.product_price, x.options_price ...." voor. Na het uitvoeren van dat stukje query heb je de volgende gegevens:

transn	product	price	optpric
1	1	10	
2	1	10
2	2	10	3

Nu kun je veilig groeperen op transactienummer zonder je zorgen te maken dat de produktprijs teveel wordt meegerekend.

[Willie-wortel]

hmmmzz..... hij geeft een fout: Unknown column 'x.productoptions_price' in 'field list'

[Voutloos]

Verwijderd schreef op zaterdag 29 maart 2008 @ 01:35:
[...]
Kan misschien in MySQL, maar 't is geen valid SQL: product_price zit in je GROUP BY, maar niet in je SELECT,

Onzin, je mag best groeperen maar niet selecteren.

en product_price + SUM( productoptions_price) AS som_prijs is geen geaggregeerde kolom, en mag dus niet in je SELECT voorkomen.

Is een sommetje van iets dat in de group by staat en een aggragate function, mag ook dus.

Overigens een topic van vandaag met heel veel overeenkomsten: [MySQL] Onverklaarbaar traag en onjuist resultaat. En daar noemt ACM ook 2 queries welke mooier zijn dan degene die hier genoemd worden.

[ Voor 6% gewijzigd door Voutloos op 31-03-2008 20:56 ]

[The Fox NL]

Willie-wortel schreef op maandag 31 maart 2008 @ 20:23:
hmmmzz..... hij geeft een fout: Unknown column 'x.productoptions_price' in 'field list'

Een typefoutje in mijn query:

SQL:

select ap.transaction_number, ap.product_price, x.options_price
    from (
      select apo.product_id, sum(apo.options_price)
      from administration_productoptions apo
      group by apo.product_id
    ) z 

moet

SQL:

select ap.transaction_number, ap.product_price, Z.options_price
    from (
      select apo.product_id, sum(apo.options_price)
      from administration_productoptions apo
      group by apo.product_id
    ) z 

zijn. (x moet z zijn)

[Dido]

Ben ik nou de eerste die nog wakker genoeg is om te zien dat de in de query van de TS genoemde "productoptions_id" in geen enkele tabel voorkomt, en dat de TS geen enkele kolom qualificeert, met andere woorden dat "transaction_number = transaction_number" hetzelfde is als "1=1"?

Als ik dan lees

Dit werkt niet aangezien hij de adminsitration_products tabel vaker meerekent...

Dan denk ik dat dat dus wel eens iets met die 1=1 te maken kan hebben. Toch grappig hoe mensen meteen met allerlei uitgebreide antwoorden gaan gooien ongetwijfeld interessant zijn, maar de TS waarschijnlijk geen steek verder helpen (bij het begrijpen van SQL).

maar goed. Als je twee gelijknamige kolommen hebt om te joinen doe je iets als

code:

SELECT a.kolomX, a.kolomY, b.kolomZ
FROM tabel1 a JOIN tabel 2 b on a.id = b.id;

Ik begrijp trouwens waarom je de options LEFT OUTER JOINt, maar waarom is die eerste niet gewoon een INNER JOIN?

edit: Zo wakker ben ik nou ook weer niet, wellicht staan er gewoon een hoop slordige fouten in de TS. Het echte "probleem" zit hem er natuurlijk in dat er inderdaad meerdere keren geteld gaat worden met die Adminprods tabel (en wel zo vaak als er options zijn).
* Dido gaat toch maar pitten

[ Voor 31% gewijzigd door Dido op 31-03-2008 23:14 ]

[Voutloos]

Dido schreef op maandag 31 maart 2008 @ 23:06:
Ben ik nou de eerste die nog wakker genoeg is om te zien

In 't topic waar ik hierboven naar link was ik ook de eerste die opmerkte dat er een cartesisch product is. Een dergelijk product hoor je echt bij beide topicstarts binnen 5 seconden te spotten. Dit is geen rocket science, maar puur de meest basale join kennis. Als je de termen (cartesisch product, en hier is tautologie ook wel een mooie) niet weet ok, maar kom op zeg, probeer zelf eens zo'n join uit te tekenen en er moet al iets gaan kriebelen.

[ Voor 5% gewijzigd door Voutloos op 01-04-2008 08:06 ]

[Willie-wortel]

in eerste instantie bedankt voor jullie reacties. Het model zou als volgt moeten werken: in de tabel transacties staan datum, tijd en werknemer etc. Een transactie heeft minimaal 1 product of meer..... ook het aantal producten en de prijs staat in de tabel. Vervolgens kan een product 1 of meerdere opties hebben, zoals gewicht 5kg, dan komt er bijvoorbeeld € 2,00 bij.

Dus het zijn twee 1 op veel relaties.

Nu zoek ik dus naar een query die als resultaat geeft:

per transactie de som van de product prijzen ( dus ook x aantal producten ) + de som van de product optie prijzen ( ook x aantal van het product ) ......

heb het in code (vb) alwel opgelost, dit werkt ook, maar het wordt een stuk overzichtelijker/makkelijker als het met 1 query kan. Al vraag ik mij wel af wat sneller is.

[_js_]

SQL:

SELECT at.transaction_number,SUM(pao.product_and_options_price)
FROM administration_transaction at
LEFT JOIN (
  SELECT ap.transaction_number,ap.product_price + SUM(ao.productoptions_price) AS product_and_options_price
  FROM administration_products ap
  LEFT JOIN administration_productoptions ao ON ap.product_id = ao.product_id
  GROUP BY ap.product_id,ap.product_price,ap.transaction_number
) pao ON at.transaction_number = pao.transaction_number
GROUP BY at.transaction_number

Wat je doet is eerst per product uit een transactie de prijs van het product + alle opties berekenen, daarna tel je per transactie de eerder gemaakte sommen op.

[Voutloos]

Willie-wortel schreef op dinsdag 01 april 2008 @ 20:04:
Dus het zijn twee 1 op veel relaties.

Nu zoek ik dus naar een query die als resultaat geeft:

per transactie de som van de product prijzen ( dus ook x aantal producten ) + de som van de product optie prijzen ( ook x aantal van het product ) ......

Voor de derde keer: Dit is dus 100% identiek met het probleem in [MySQL] Onverklaarbaar traag en onjuist resultaat. Daar staan een paar queries die je dus al had kunnen proberen.

[Woy]

Ik zou ongeveer het volgend doen denk

SQL:

select t.transaction_number, sum( tmp.product_price ) + sum( tmp.options_price ) as price
from(   select p.product_id, p.transaction_number, p.product_price, sum( po.productoptions_price ) as options_price
        from administratation_products p
        inner join administration_productoptions po
            on  p.product_id = po.product_id
        group by p.product_id, p.transaction_number, p.product_price ) as tmp
inner join administration_transactions t
    on t.transaction_number = tmp.transaction_number
group by t.transaction_number

Als mysql geen sub_query's ondersteund maar wel views zou je van dit gedeelte

SQL:

select p.product_id, p.transaction_number p.product_price, sum( po.productoptions_price ) as options_price
from administratation_products p
inner join administration_productoptions po
    on  p.product_id = po.product_id
group by p.product_id, p.transaction_number, p.product_price

nog een view kunnen maken.

[ Voor 19% gewijzigd door Woy op 02-04-2008 09:30 ]