[MySQL] Subquery uitkomst gebruiken in WHERE

[Vinze]

Ik loop een beetje vast bij het opbouwen van een query om een overzicht te krijgen van alle projecten.
Om alle projecten uit de database te halen, met daarbij het totaal aantal activiteiten en het aantal afgeronde activiteiten, gebruik ik de volgende query:

MySQL:

SELECT p.*, k.klantnaam,
(SELECT COUNT(project_id) FROM activiteiten AS a WHERE a.project_id = p.ID AND a.afgerond = 1) AS activiteiten_afgerond,
(SELECT COUNT(project_id) FROM activiteiten AS a WHERE a.project_id = p.ID) AS activiteiten_totaal
FROM projecten AS p
LEFT JOIN klanten AS k ON k.ID = p.klant_id
WHERE p.status != 'Afgerond & gesloten' AND activiteiten_totaal != 0
ORDER BY p.ID DESC
LIMIT 1000

Nu krijg ik de volgende foutmelding: Unknown column 'activiteiten_totaal' in 'where clause'

Ik ben inmiddels al de hele middag aan het Googlen en van alles aan het proberen maar ik kom er niet uit.
Waarom kan ik de uitkomst van een subquery niet gebruiken in combinatie met WHERE? Tips over hoe ik dit kan oplossen, of is er een nettere manier om tot hetzelfde resultaat te komen?

Alvast bedankt!

[HyperioN]

HAVING

[Vinze]

Goh, had ik deze vraag nou maar wat eerder gesteld, dat had me heel wat zoekwerk bespaard..
Mijn dank is groot!

Edit: En mocht iemand in de toekomst dit topic vinden, de oplossing toegepast:

MySQL:

SELECT p.*, k.klantnaam, 
(SELECT COUNT(project_id) FROM activiteiten AS a WHERE a.project_id = p.ID AND a.afgerond = 1) AS activiteiten_afgerond, 
(SELECT COUNT(project_id) FROM activiteiten AS a WHERE a.project_id = p.ID) AS activiteiten_totaal 
FROM projecten AS p 
LEFT JOIN klanten AS k ON k.ID = p.klant_id 
WHERE p.status != 'Afgerond & gesloten'
HAVING activiteiten_totaal != 0 
ORDER BY p.ID DESC 
LIMIT 1000

[ Voor 69% gewijzigd door Vinze op 17-03-2012 17:03 ]

[Rotterdammertje]

En nu is de vraag: waarom werkt 'activiteiten_totaal' in de WHERE clause niet, maar in de HAVING clause wel? Uw tijd gaat NU in!

[Fiander]

Rotterdammertje schreef op maandag 19 maart 2012 @ 10:06:
En nu is de vraag: waarom werkt 'activiteiten_totaal' in de WHERE clause niet, maar in de HAVING clause wel? Uw tijd gaat NU in!

Bij queries is er een vaste volgorde van uitvoeren.
1 FROM + alle joins
2 WHERE
3 GROUP by en HAVING
4 ORDER
5 SELECT

Omdat de WHERE al reeds is uitgevoerd voordat de 'activiteiten_totaal' bestaat, kun je deze "kolom" niet gebruiken in de WHERE.
De HAVING doet zijn eigen berekening, en is dus hier wel bruikbaar ( net zoals een subquerie in de where ookm zou hebben gewerkt )

[Rotterdammertje]

Wat op zich een beetje vreemd is; je zou verwachten dat 'activiteiten_totaal' pas wordt aangemaakt bij het uitvoeren van de SELECT. Maar blijkbaar wordt de alias al eerder gedefinieerd, waardoor je 'm wel kan gebruiken in de HAVING clause..

[Fiander]

Rotterdammertje schreef op maandag 19 maart 2012 @ 16:10:
Wat op zich een beetje vreemd is; je zou verwachten dat 'activiteiten_totaal' pas wordt aangemaakt bij het uitvoeren van de SELECT. Maar blijkbaar wordt de alias al eerder gedefinieerd, waardoor je 'm wel kan gebruiken in de HAVING clause..

nee.

binnen de having moet je de berekening opnieuw doen. ( of chronologisch gezien eerder )

dus :
1 ) SELECT naam, COUNT(*)
2 ) FROM tabel01
3 ) WHERE id >=0 AND id < 1000
4 ) GROUP BY naam
5 ) HAVING COUNT(*) > 2

2) geeft uit tabel tabel01
3) waar de ID's tussen 0 en 1000 liggen
4) Gegroepeerd op naam
5) waarbij de groupset meer items heeft dan 2
1) de naam weer met het aantal
maar dit mag ook

1 ) SELECT naam
2 ) FROM tabel01
3 ) WHERE id >=0 AND id < 1000
4 ) GROUP BY naam
5 ) HAVING COUNT(*) > 2

MySQL:

SELECT p.*, k.klantnaam, 
(SELECT COUNT(project_id) FROM activiteiten AS a WHERE a.project_id = p.ID AND a.afgerond = 1) AS activiteiten_afgerond, 
(SELECT COUNT(project_id) FROM activiteiten AS a WHERE a.project_id = p.ID) AS activiteiten_totaal 
FROM projecten AS p 
LEFT JOIN klanten AS k ON k.ID = p.klant_id 
WHERE p.status != 'Afgerond & gesloten'
HAVING activiteiten_totaal != 0 
ORDER BY p.ID DESC 
LIMIT 1000

Dat deze querie werk is gewoon een van de vele fouten in mySql mbt Group by functionaliteit.
zonet gecontroleerd op MSSQL 2005/2008/2008R2/2012 en Oracle 11 en die werken allemaal netjes correct.

[ Voor 33% gewijzigd door Fiander op 19-03-2012 20:21 ]

[Gomez12]

Fiander schreef op maandag 19 maart 2012 @ 19:41:
[...]
Dat deze querie werk is gewoon een van de vele fouten in mySql mbt Group by functionaliteit.
zonet gecontroleerd op MSSQL 2005/2008/2008R2/2012 en Oracle 11 en die werken allemaal netjes correct.

Kleine correctie, het is niet direct een fout van mysql. Het is een fout van de sql-spec makers die dit gat open lieten, mysql heeft enkel een short-cut binnen de specs genomen.

Sowieso is het maar net de vraag of de query werkt zoals gewenst binnen mysql. Wat je terugkrijgt is undefined, dat kan 2 jaar lang goed gaan en opeens verricht iemand onderhoud aan de mysql server en opeens gaat het fout en dan heb je nog geluk. Met een beetje pech gaat er soms iets ongewenst mee...

Wat altijd een leuke test-case is : maak een sql-dump, randomize daarin de insert regels en gooi die dump dan weer terug in een test-dbase.

[pedorus]

Vinze schreef op zaterdag 17 maart 2012 @ 16:59:
Edit: En mocht iemand in de toekomst dit topic vinden, de oplossing toegepast:

MySQL:

SELECT p.*, k.klantnaam, 
(SELECT COUNT(project_id) FROM activiteiten AS a WHERE a.project_id = p.ID AND a.afgerond = 1) AS activiteiten_afgerond, 
(SELECT COUNT(project_id) FROM activiteiten AS a WHERE a.project_id = p.ID) AS activiteiten_totaal 
FROM projecten AS p 
LEFT JOIN klanten AS k ON k.ID = p.klant_id 
WHERE p.status != 'Afgerond & gesloten'
HAVING activiteiten_totaal != 0 
ORDER BY p.ID DESC 
LIMIT 1000

Hmm,het lijkt me toch handiger om te joinen op activiteiten en een group by te doen, en a.afgerond op te tellen/count(*) te gebruiken. Die subqueries zijn niet zo nodig hier en zouden wel eens wat tijd kunnen kosten..