[MySQL] NULL-waarden niet groeperen

[djc]

Okay, ik heb een probleempje met een MySQL-query.

Ik heb tabellen polls, poptions, pvotes. Nu wil ik een query maken die voor een bepaalde poll alle opties en het aantal votes voor die optie ophaalt. Ik bedacht het volgende, maar dat werkt niet:

SELECT pl.title, po.id, po.txt, pv.user, pv.poll, pv.vote, COUNT(pv.vote) AS votes FROM polls pl, poptions po LEFT JOIN pvotes pv ON pv.vote = po.id WHERE pl.id = po.poll GROUP BY pv.vote

Waarom het niet werkt: als binnen een poll op meerdere opties nog niet gestemd is, geeft pv.vote voor al die opties NULL terug, waardoor de verschillende opties worden samengenomen door de GROUP BY clause. Da's niet zo mooi, want ik wil toch echt in alle gevallen alle opties laten zien (ook als op sommige opties nog niet gestemd is).

Nu kan ik dit waarschijnlijk wel oplossen met een subquery, maar weet iemand wellicht een betere oplossing? Ik gebruik MySQL 4.1.

[P_de_B]

Standaard regel bij GROUP BY: alle velden die in de selectlist staan moeten of in de GROUP BY staan, of zijn opgenomen in een aggregate functie. Dat MySQL het anders ook goedkeurt is dom, en leidt tot dit soort problemen,

P&W FAQ - SQL

[djc]

Kan je me uitleggen hoe dat hier uitmaakt?

Voor gegroepeerde kolommen is zijn pl.title, po.id en po.txt sowieso hetzelfde. Ik kan daar dus gewoon een MIN() of MAX() omheen zetten om de waarden te krijgen die ik nodig heb. De pv.* velden die ik in de selectlist heb opgenomen had ik alleen nodig om te debuggen, eigenlijk heb ik genoeg aan de COUNT(pv.vote).

[cowgirl]

Iets in poptions moet een specifiek antwoord voorstellen, door dat antwoord in je groupby te zetten krijg je dus ook de count per antwoord.

[djc]

Goed punt!! Thanks, dom dat ik dat niet zag.

[P_de_B]

Als je het op de 'goede' manier doet krijg je in ieder geval niet resultaten die je niet verwacht. Daarnaast groepeer je op pv.Vote terwijl dat juist de kolom is die je wilt tellen. Ik neem aan dat je op bijv. pl.title en po.id wilt groeperen.

In dit geval wil je wel alle opties laten zien, ook als er nog niet is gestemd. Ik ga er vanuit dat poptions altijd al is gevuld met de geldige options:

SQL:

SELECT pl.Title, po.txt, COUNT(pv.vote)
FROM polls pl
INNER JOIN poptions po on pl.id= po.poll
LEFT OUTER JOIN pvotes pv on pv.vote = po.id
GROUP BY pl.Title, po.txt

[djc]

De query die ik nu gebruikt heb:

SELECT pl.title, po.id, po.txt, COUNT(pv.vote) AS votes FROM polls pl, poptions po LEFT JOIN pvotes pv ON pv.vote = po.id WHERE pl.id = po.poll GROUP BY po.id

[P_de_B]

als je nu dezelfde poloptie hebt bij verschillende polls is het resultaat niet correct...

[djc]

Dat heb ik niet, voor iedere poll worden nieuwe opties aangemaakt.

[P_de_B]

Maar je hanteert nog steeds een verkeerde GROUP BY. Niet om te zeiken, maar geen enkele fatsoenlijke database accepteert de query die je nu hebt. Ik zou toch eens op een rustig moment inlezen in de werking van group by.

[djc]

Ik snap de werking van GROUP BY. Ik zou mijn query kunnen aanpassen zodat ik pl.title en po.txt ook groepeer, maar dat is triviaal! Waarom zou ik dat dan doen?

[P_de_B]

Manuzhai schreef op dinsdag 21 maart 2006 @ 13:23:
Ik snap de werking van GROUP BY. Ik zou mijn query kunnen aanpassen zodat ik pl.title en po.txt ook groepeer, maar dat is triviaal! Waarom zou ik dat dan doen?

Omdat je nu niet weet welke title je terugkrijgt. Het is gewoon raar dat MySQL deze query accepteert. Prima als je er in de huidige situatie er geen last van hebt, ik wilde je -wellicht ten overvloede- wijzen op het correct gebruik van group by.

[djc]

Ik had gisteren later nog een bug ontdekt in mijn query, misschien dat je hem dan beter kan appreciëren:

SELECT pl.title, po.id, po.txt, COUNT(pv.vote) AS votes FROM polls pl, poptions po LEFT JOIN pvotes pv ON pv.vote = po.id WHERE pl.id = '$topic[poll]' AND pl.id = po.poll GROUP BY po.id

Is ie dan wel correct volgens jouw criteria?

[cowgirl]

Nee, correct is de query alleen indien alle velden behalve de aggregate functie in de group by staan. Afgezien van het feit dat het nu werkt zoals verwacht, kan je in de toekomst met onderhoud problemen krijgen. Stel dat je, om welke reden dan ook, migreert naar een andere database, dan zit je raar te kijken dat je query niet meer werkt.
Het is gewoon netter om het nu al correct te doen. Het zijn 18 extra karakters intypen.