Twee SUMS met verschillende condities

Door Dylan PHP op 15-02-2014 12:57

1.611 views

Ik heb 2 tabellen. De ene tabel is 'adverts' en de andere tabel 'advertsitems'. Het is een one to many relatie dus er kunnen meerdere items aan een advert hangen.

De volgende query heb ik nu

SELECT a.title AS adtitle, a.id AS adid, a.price, a.image AS image, a.description, SUM(ai.quantity) as quantity, a.shipping, a.customs, a.createdate, a.image
FROM adverts a
LEFT JOIN advertsitems ai ON a.id = ai.advertid
WHERE 1=1 AND ai.country LIKE '%AF%'
GROUP BY a.id
ORDER BY a.id DESC

Zoals je ziet telt 'SUM(ai.quantity)' de hoeveelheid. Zonder de WHERE conditie 'AND ai.country LIKE' krijg ik als result bijvoorbeeld 6 en met deze WHERE conditie 2. Nu wil ik dus twee SUMS terug krijgen. De eerste SUM die telt aan de hand van ai.country staat er al. Nu wil ik een tweede SUM hebben die alleen naar de GROUP BY a.id luistert dus dat ik 2 en 6 terug krijgt uit dezelfde query in 2 aparte kolommen.

Notitie: WHERE 1=1 staat erin omdat de query dynamisch gebuild wordt en dit makkelijker is om voor mij WHERE conditions te plaatsen.

Iemand die mij kan/wil helpen? Bedankt alvast :)

Ivo P

15-02-2014 13:17

http://wiki.pfz.nl/group-by voor wat meer begrip van GROUP BY

Dylan PHP

15-02-2014 13:32

Ik heb het artikel gelezen maar kom niet verder. Als jij weet hoe ik dit moet oplossen in mijn query hoor ik dat graag! iig bedankt voor het reageren :)

Ivo P

15-02-2014 14:34

eigenlijk is de basisregel die in dat artikel staat vrij simpel:

"alle kolommen die in SELECT staan en niet een aggregatie functie zijn (sum, avg, max, count etc) moeten in GROUP BY staan."

of als je het liever zo leest:

"alleen kolommen die in GROUP BY staan, mogen samen met functies als SUM, AVG etc in SELECT geplaatst worden".

Waarschijnlijk krijg je in dit geval hetzelfde antwoord.

---
ik heb nu pas de rest van je vraag begrepen:

Je wilt dus een sum() over alles en een sum() over alleen "where kolomx = 10 "


SELECT SUM(kolom) AS som1, 
       SUM(kolom * (kolomx = 10)) AS som2
FROM ...

(kolomx = 10) levert namelijk true of false op, maar dat vertaalt zich ook naar 1 of 0

dus voor de rijen waar kolomx bijvoorbeeld 12 is, wordt dat kolom * 0 = 0

Dylan PHP

15-02-2014 19:24

Dat werkt helaas niet omdat de WHERE condition 'ai.country LIKE' van toepassing is. Ik zoek dus een oplossing om een SUM te doen zonder de WHERE condition maar alleen op basis van de GROUP BY (dus a.id = ai.advertid)

Ivo P

15-02-2014 19:41

SUM(kolom * (ai.country LIKE 'Ne%')) AS som2

zou ook gewoon moeten werken

Dylan PHP

15-02-2014 20:08

SELECT a.title AS adtitle, a.id AS adid, a.price, a.image AS image, a.description, SUM( ai.quantity ) AS quantity, a.shipping, a.customs, a.createdate, a.image, SUM( ai.quantity * ( ai.country LIKE  '%AF%' ) ) AS quantity2
FROM adverts a
LEFT JOIN advertsitems ai ON a.id = ai.advertid
WHERE 1 =1
AND ai.country LIKE  '%AF%'
GROUP BY a.id
ORDER BY a.id DESC

Ik krijg het niet voor elkaar, ik krijg het volgende
quantity = 2
quantity2 = 2
Terwijl quantity2 6 moet returnen.

Anyway, thanks dat je me helpt :)

Ivo P

15-02-2014 21:32

regel 5 moet je dan weglaten: anders krijg je nooit die 4 uit landen zonder AF in de naam.

verder moet je je regel groupby in principe nog flink uitbreiden.

Dylan PHP

15-02-2014 21:35 gewijzigd op 15-02-2014 21:36

Het werkt nu, ik gebruik de volgende query


SELECT a.title AS adtitle, a.id AS adid, a.price, a.image AS image, a.description,
       SUM(ai.quantity) as totalquantity,
       SUM(CASE WHEN ai.country LIKE '%AF%' THEN quantity ELSE 0 END) as quantity,
      a.shipping, a.customs, a.createdate, ai.country
FROM adverts a
LEFT JOIN advertsitems ai ON a.id = ai.advertid
WHERE 1=1
GROUP BY a.id
HAVING ai.country LIKE '%AF%' 
ORDER BY a.id DESC

Ivo P

15-02-2014 21:42

ik dacht eigenlijk aan


SELECT a.title AS adtitle, a.id AS adid, a.price, a.image AS image, a.description,
       SUM(ai.quantity) as totalquantity,
       SUM(CASE WHEN ai.country LIKE '%AF%' THEN quantity ELSE 0 END) as quantity,
      a.shipping, a.customs, a.createdate, ai.country
FROM adverts a
LEFT JOIN advertsitems ai ON a.id = ai.advertid
WHERE 1=1
GROUP BY a.title AS adtitle, a.id AS adid, a.price, a.image AS image, a.description,
         a.shipping, a.customs, a.createdate, ai.country
ORDER BY a.id DESC

Reageren

Inloggen om te reageren