[MySQL] Problemen met optellen van verschillende totalen

[FRoSTy]

Ik zit met het volgende probleem, namelijk dat ik een tabel heb met alle bestellingen die klanten gedaan hebben in de afgelopen jaren.
Nou wil ik er een overzicht uit hebben van welke omzet we gehad hebben per jaar per klant, wil graag een recordlijst hebben, met klant_id, omzet2005, omzet2006, omzet2007.
Hiervoor gebruik ik nu de volgende query:

SELECT BESTEL_CUST_ID,
IF(DATE_FORMAT(BESTEL_DATUM, '%Y') = 2005, SUM(BESTEL_TOTP),NULL) AS TOT2005,
IF(DATE_FORMAT(BESTEL_DATUM, '%Y') = 2006, SUM(BESTEL_TOTP),NULL) AS TOT2006,
IF(DATE_FORMAT(BESTEL_DATUM, '%Y') = 2007, SUM(BESTEL_TOTP),NULL) AS TOT2007
FROM `TBL_USER_BESTEL` GROUP BY BESTEL_CUST_ID

alleen het probleem is nu dat zodra ik group by BESTEL_CUST_ID doe, ik altijd maar een variabele gevuld heb, dus als TOT2005 een waarde heeft, hebben TOT2006 en TOT2007 geen waarde, zoals hieronder bv:

BESTEL_CUST_ID TOT2005 TOT2006 TOT2007
4 56103 NULL NULL
5 143 NULL NULL
8 5174 NULL NULL
9 507996 NULL NULL

Heb al vanalles geprobeerd, maar het wil maar niet lukken. Misschien dat iemand hier een oplossing voor weet, of dat ik het op een compleet andere manier moet oplossen. Alle ideeen zijn welkom

[P.O. Box]

je moet ook doen:
(evnetueel uitgeschrevne, weet niet of MySQL het anders pakt)

SQL:

group by bestel_cust_id, TOT2005, TOT2006, TOT2007

[ Voor 51% gewijzigd door P.O. Box op 05-06-2007 17:06 ]

[toost]

ah edwardvb was me voor.

ps. ik gebruik YEAR(BESTEL_DATUM) = 2005, doet het zelfde alleen is korter en overzichtelijker


edit: edward maar wat als er bv. geen tot2007 zou zijn, dan krijg je volgens mij een error in je SQL (weet het niet zeker)

[ Voor 62% gewijzigd door toost op 05-06-2007 17:12 ]

[_js_]

FRoSTy schreef op dinsdag 05 juni 2007 @ 16:57:
SELECT BESTEL_CUST_ID,
IF(DATE_FORMAT(BESTEL_DATUM, '%Y') = 2005, SUM(BESTEL_TOTP),NULL) AS TOT2005,
IF(DATE_FORMAT(BESTEL_DATUM, '%Y') = 2006, SUM(BESTEL_TOTP),NULL) AS TOT2006,
IF(DATE_FORMAT(BESTEL_DATUM, '%Y') = 2007, SUM(BESTEL_TOTP),NULL) AS TOT2007
FROM `TBL_USER_BESTEL` GROUP BY BESTEL_CUST_ID

Wat doe je hier?

Je controleert het jaar één maal, en doet dan een SUM over alles, blijkbaar is net die ene controle over een jaar dat in 2005 valt. Wat je wilt is over elke afzonderlijke regel kijken of deze in 2005 valt en dan al dan niet optellen.

SUM(IF(jaar = 2005 ,waarde,0))

[FRoSTy]

Ah YEAR idd handiger
Maar als ik die group by aanpas naar zoals hieronder

SELECT BESTEL_CUST_ID,
IF(YEAR(BESTEL_DATUM) = 2005, SUM(BESTEL_TOTP),NULL) AS TOT2005,
IF(YEAR(BESTEL_DATUM) = 2006, SUM(BESTEL_TOTP),NULL) AS TOT2006,
IF(YEAR(BESTEL_DATUM) = 2007, SUM(BESTEL_TOTP),NULL) AS TOT2007
FROM `TBL_USER_BESTEL` GROUP BY BESTEL_CUST_ID, TOT2005, TOT2006, TOT2007

dan krijg ik
#1056 - Can't group on 'TOT2005'

[FRoSTy]

_js_ schreef op dinsdag 05 juni 2007 @ 17:12:
[...]

Wat doe je hier?

Je controleert het jaar één maal, en doet dan een SUM over alles, blijkbaar is net die ene controle over een jaar dat in 2005 valt. Wat je wilt is over elke afzonderlijke regel kijken of deze in 2005 valt en dan al dan niet optellen.

SUM(IF(jaar = 2005 ,waarde,0))

Tnx ik replyde tegelijk met jou op het topic daardoor had ik um net gemist, maar kep ut nu idd als volgt opgelost

SELECT BESTEL_CUST_ID,
SUM(IF(YEAR(BESTEL_DATUM) = 2005, BESTEL_TOTP,0)) AS TOT2005,
SUM(IF(YEAR(BESTEL_DATUM) = 2006, BESTEL_TOTP,0)) AS TOT2006,
SUM(IF(YEAR(BESTEL_DATUM) = 2007, BESTEL_TOTP,0)) AS TOT2007
FROM `TBL_USER_BESTEL` GROUP BY BESTEL_CUST_ID

Alleen nou wil ik eigenlijk nog sorteren op
ORDER BY (((TOT2007/TOT2006)*100)-100)
alleen dat mag niet, want dan krijg ik de melding
Reference 'TOT2007' not supported (reference to group function)

Heb het al opgelost door maar dit te doen:

SELECT BESTEL_CUST_ID,
SUM(IF(YEAR(BESTEL_DATUM) = 2005, BESTEL_TOTP,0)) AS TOT2005,
SUM(IF(YEAR(BESTEL_DATUM) = 2006, BESTEL_TOTP,0)) AS TOT2006,
SUM(IF(YEAR(BESTEL_DATUM) = 2007, BESTEL_TOTP,0)) AS TOT2007,
COALESCE(((SUM(IF(YEAR(BESTEL_DATUM) = 2007, BESTEL_TOTP,0))/SUM(IF(YEAR(BESTEL_DATUM) = 2006, BESTEL_TOTP,0)))*100)-100,0) AS VERSCHIL
FROM `TBL_USER_BESTEL` GROUP BY BESTEL_CUST_ID ORDER BY VERSCHIL ASC

[ Voor 32% gewijzigd door FRoSTy op 06-06-2007 10:53 ]

[jvdmeer]

FRoSTy schreef op woensdag 06 juni 2007 @ 09:29:
[...]


Tnx ik replyde tegelijk met jou op het topic daardoor had ik um net gemist, maar kep ut nu idd als volgt opgelost

SELECT BESTEL_CUST_ID,
SUM(IF(YEAR(BESTEL_DATUM) = 2005, BESTEL_TOTP,0)) AS TOT2005,
SUM(IF(YEAR(BESTEL_DATUM) = 2006, BESTEL_TOTP,0)) AS TOT2006,
SUM(IF(YEAR(BESTEL_DATUM) = 2007, BESTEL_TOTP,0)) AS TOT2007
FROM `TBL_USER_BESTEL` GROUP BY BESTEL_CUST_ID

Alleen nou wil ik eigenlijk nog sorteren op
ORDER BY (((TOT2007/TOT2006)*100)-100)
alleen dat mag niet, want dan krijg ik de melding
Reference 'TOT2007' not supported (reference to group function)

Heb het al opgelost door maar dit te doen:

SELECT BESTEL_CUST_ID,
SUM(IF(YEAR(BESTEL_DATUM) = 2005, BESTEL_TOTP,0)) AS TOT2005,
SUM(IF(YEAR(BESTEL_DATUM) = 2006, BESTEL_TOTP,0)) AS TOT2006,
SUM(IF(YEAR(BESTEL_DATUM) = 2007, BESTEL_TOTP,0)) AS TOT2007,
COALESCE(((SUM(IF(YEAR(BESTEL_DATUM) = 2007, BESTEL_TOTP,0))/SUM(IF(YEAR(BESTEL_DATUM) = 2006, BESTEL_TOTP,0)))*100)-100,0) AS VERSCHIL
FROM `TBL_USER_BESTEL` GROUP BY BESTEL_CUST_ID ORDER BY VERSCHIL ASC

Gewoon oplossen met ORDER BY 4 (=kolomnr)

[FRoSTy]

jvdmeer schreef op woensdag 06 juni 2007 @ 15:26:
[...]


Gewoon oplossen met ORDER BY 4 (=kolomnr)

Ja alleen volgens mij kan ik dan geen berekeningen meer uitvoeren met die kolom dus bv 4/3 want dan is het gewoon 1,333

[jvdmeer]

FRoSTy schreef op donderdag 07 juni 2007 @ 09:49:
[...]


Ja alleen volgens mij kan ik dan geen berekeningen meer uitvoeren met die kolom dus bv 4/3 want dan is het gewoon 1,333

Nee, in MS-SQL kan je het op de volgende 'lelijke'manier oplossen:

code:

SELECT * FROM (
  SELECT BESTEL_CUST_ID,
  SUM(IF(YEAR(BESTEL_DATUM) = 2005, BESTEL_TOTP,0)) AS TOT2005,
  SUM(IF(YEAR(BESTEL_DATUM) = 2006, BESTEL_TOTP,0)) AS TOT2006,
  SUM(IF(YEAR(BESTEL_DATUM) = 2007, BESTEL_TOTP,0)) AS TOT2007
  FROM `TBL_USER_BESTEL` GROUP BY BESTEL_CUST_ID
) x
ORDER BY (((TOT2007/TOT2006)*100)-100)

Ik weet niet of dat bij MySQL werkt.