[mySQL] SELECT totalen uit verschillende tabellen

[EmilneM]

Er zijn twee tabellen in een MySQL-database

ORDERS

OrderID	KlantID	AdmKosten
1	1	10
2	1	5

ORDERREGELS

OrderID	ArtikelID	Prijs
1	1	25
1	2	50
2	1	25
2	2	50

Nu wil ik met één select uit de database het totaal van AdmKosten en het totaal van Prijs hebben voor de klant met KlantID = 1, dus de totalen van beide orders.

Ik heb het als volgt geprobeerd:

SQL:

SELECT
     SUM(O.AdmKosten) AS AdmTot, 
     SUM(R.Prijs) AS AdmTot
FROM
     orders O 
LEFT JOIN 
     orderregels R ON (O.OrderID = R.OrderID)
WHERE
     O.KlantID = 1
GROUP BY
     O.KlantID

Resultaat is dan het volgende:

AdmTot	PrijsTot
30	150

PrijsTot is netjes een optelling van de prijzen. Deze waarde klopt. AdmTot klopt echter niet, dit is ook logisch want door de join komt hij de AdmKosten in elke regel tegen.

Is er een manier om beide totalen correct te krijgen binnen een enkele select?

[NMe]

Volgens mij kan dit alleen als je versie 4.1 of hoger hebt, aangezien je dan een subquery nodig gaat hebben, voor zover ik kan zien althans.

[justmental]

Misschien kan ie zoiets?:

code:

select sum(sum(r.prijs)+o.admkosten)
from orders o
...
group by o.orderid

[marty]

justmental schreef op woensdag 19 januari 2005 @ 14:40:
Misschien kan ie zoiets?:

code:

select sum(sum(r.prijs)+o.admkosten)
from orders o
...
group by o.orderid

die sum(sum(r.prijs)+o.admkosten) is volgens mij niet wat ie wil, maar wil ie gewoon apart de totalen van de admKosten en prijzen hebben.

Volgens mij gaat dit inderdaad niet zonder subquery (of temp table) werken.

Waarom heb je trouwens de prijs in de orderregels staan? Staat die niet al in een andere tabel (die via het ArtikelID is terug te vinden)

[EmilneM]

marty schreef op woensdag 19 januari 2005 @ 15:24:
[...]
Waarom heb je trouwens de prijs in de orderregels staan? Staat die niet al in een andere tabel (die via het ArtikelID is terug te vinden)

Voor de historie. Als er prijswijzigingen zijn mag dat niet in orders doorwerken die in het verleden liggen.

[marty]

nog even nagedacht en ... echt mooi is het niet, maar volgens mij zou dit nog wel eens kunnen werken

[...] werkt niet

[ Voor 54% gewijzigd door marty op 19-01-2005 15:50 ]

[EmilneM]

Heeft er nog iemand een idee?

[Verwijderd]

Volgens mij kan het inderdaad niet in 1 SELECT Query aangezien je SUM niet DISTINCT kan optellen.

[bigbeng]

Misschien dat dit werkt:

code:

SELECT SUM(o.admkosten)/(COUNT(r.prijs)/COUNT(o.admkosten)) AS AdmTot,
SUM(r.prijs) As PrijsTot
FROM orders o
LEFT JOIN orderregels r ON (o.orderid=r.orderid)
WHERE o.klantid=1
GROUP BY o.klantid

Potentieel probleem: prijs=0 of admkosten=0, dit zou leiden tot divide by zero.
Overigens heb ik geen mysql, dus ik heb dit niet kunnen testen.

[ACM]

Als je mysql 4.0 of hoger gebruikt kun je wellicht beter een union gebruiken:

SQL:

(
 SELECT
     SUM(O.AdmKosten) AS AdmTot
 FROM
    orders O 
 WHERE
      klantid = 1
)
UNION 
(
 SELECT
     SUM(R.Prijs) AS AdmTot
 FROM
     orders O 
 LEFT JOIN 
     orderregels R ON (O.OrderID = R.OrderID)
 WHERE
     O.KlantID = 1
 GROUP BY
      O.KlantID
)

(haakjes zijn niet verplicht, wellicht zelfs fout, dit is ongetest)

[ Voor 9% gewijzigd door ACM op 20-01-2005 12:03 ]

[marty]

bigbeng schreef op donderdag 20 januari 2005 @ 11:55:
Misschien dat dit werkt:

code:

SELECT SUM(o.admkosten)/(COUNT(r.prijs)/COUNT(o.admkosten)) AS AdmTot,
SUM(r.prijs) As PrijsTot
FROM orders o
LEFT JOIN orderregels r ON (o.orderid=r.orderid)
WHERE o.klantid=1
GROUP BY o.klantid

Potentieel probleem: prijs=0 of admkosten=0, dit zou leiden tot divide by zero.
Overigens heb ik geen mysql, dus ik heb dit niet kunnen testen.

Dit gaat niet werken. Beide COUNTs zullen het totaal van het aantal rows zijn (voor groepering) (4 in dit geval) en dus deel je uiteindelijk gewoon door 1

[EmilneM]

ACM schreef op donderdag 20 januari 2005 @ 12:02:
Als je mysql 4.0 of hoger gebruikt kun je wellicht beter een union gebruiken:

SQL:

(
 SELECT
     SUM(O.AdmKosten) AS AdmTot
 FROM
    orders O 
 WHERE
      klantid = 1
)
UNION 
(
 SELECT
     SUM(R.Prijs) AS AdmTot
 FROM
     orders O 
 LEFT JOIN 
     orderregels R ON (O.OrderID = R.OrderID)
 WHERE
     O.KlantID = 1
 GROUP BY
      O.KlantID
)

(haakjes zijn niet verplicht, wellicht zelfs fout, dit is ongetest)

Resultaat is twee rijen. In real life select ik nog meer velden mee dus dat is geen optie.

[ArcticTiger]

maar waarom perse in 1 select?
misschien een achterlijke vraag?

[EmilneM]

ArcticTiger schreef op donderdag 20 januari 2005 @ 17:21:
maar waarom perse in 1 select?
misschien een achterlijke vraag?

Omdat er meerdere rijen worden geselect. Het gaat om facturen waarin totaaltellingen worden opgenomen.

[NetForce1]

In Access werkt dit:

SQL:

SELECT A.klantid,  (totkosten+totprijs) AS admtot
FROM (
  SELECT klantid, SUM(admkosten) AS totkosten
  FROM Orders AS O
  GROUP BY klantid ) AS A
, (
  SELECT klantid, SUM(prijs) AS totprijs
  FROM Orders AS O
  LEFT JOIN Orderregels AS R ON (R.orderid = O.orderid)
  GROUP BY klantid ) AS B
WHERE A.klantid=B.klantid
;

[ Voor 6% gewijzigd door NetForce1 op 20-01-2005 19:05 . Reden: hij pakt nu alle klanten ]