[mysql] GROUP BY & WHERE foutje?
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT `hotels`.*,
(SELECT AVG(rating)
FROM hotelreviews WHERE hotel_id = hotels.id
WHERE (rating IS NOT NULL AND rating > 0)
GROUP BY hotel_id) AS `avgrating`
FROM `hotels`
WHERE (id = '2791')
(SELECT AVG(rating)
FROM hotelreviews WHERE hotel_id = hotels.id
WHERE (rating IS NOT NULL AND rating > 0)
GROUP BY hotel_id) AS `avgrating`
FROM `hotels`
WHERE (id = '2791')
Als ik de 'WHERE' weglaat uit de subquery, gaat het goed, echter wanneer ik de voorwaarden toevoeg geeft mysql een fout. Zo'n typische fout waarmee je denkt, wat is er?
Code (php)
1
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (rating IS NOT NULL AND rating > 0) GROUP BY hotel_id) AS `avgrating` FROM' at line 1
Gewijzigd op 01/01/1970 01:00:00 door Storeman storeman
SELECT hotels.* , AVG(rating) AS avgrating
FROM hotels AS h
LEFT JOIN hotelreviews AS hr
ON h.id=hr.hotel_id
WHERE hr.rating IS NOT NULL AND hr.rating > 0 AND h.id=2791
GROUP BY h.id
@Tikkes: Query heb ik even vanuit phpmyadmin geplakt, ik gebruik het zelf nooit.
@Bastiaan: Zou ook kunnen, maar heb je een argument waarom dat beter zou zijn? Ik vind het namelijk een beetje dom werk als je eerst je recordset gaat uitbreiden (dus ook alle hotelinformatie over meerdere rijen) en vervolgens weer gaat samenvoegen naar 1 rij
storeman schreef op 20.06.2009 12:32:
Ja, omdat het vele malen sneller is dan voor elk record een aparte subquery uit te voeren om het gemiddelde te bepalen.maar heb je een argument waarom dat beter zou zijn?
Als je overiges de query van Bastiaan gebruikt, moet je voor de volledigheid wel groeperen op alle kolommen die je in je SELECT query ophaalt. Gebruik dus geen * maar geef de kolomnamen op en voeg die tevens toe aan de GROUP BY.
Dus iets als:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
h.naam,
h.adres,
h.etc,
AVG(hr.rating) AS avgrating
FROM
hotels AS h
LEFT JOIN
hotelreviews AS hr
ON hr.hotel_id = h.id
WHERE
hr.rating IS NOT NULL
AND
hr.rating > 0
AND
h.id = 2791
GROUP BY
h.naam,
h.adres,
h.etc
h.naam,
h.adres,
h.etc,
AVG(hr.rating) AS avgrating
FROM
hotels AS h
LEFT JOIN
hotelreviews AS hr
ON hr.hotel_id = h.id
WHERE
hr.rating IS NOT NULL
AND
hr.rating > 0
AND
h.id = 2791
GROUP BY
h.naam,
h.adres,
h.etc
Uiteindelijk halen beide queries natuurlijk evenveel data op en is de dataset dus even groot. Het verschil is alleen of je het in 1 keer haalt of in stapjes. Mijn gedachtegang is dat je leiver je data eerst haalt en dan berekeningen doet dan per hotel een query te draaien voor subdata aangezien dit resulteert in veel meer queries.
Gewijzigd op 01/01/1970 01:00:00 door Bastiaan
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT hotels.*,
(SELECT AVG(rating)
FROM hotelreviews WHERE hotel_id = hotels.id
WHERE (rating IS NOT NULL AND rating > 0)
GROUP BY hotel_id) AS avgrating)
FROM hotels
WHERE (id = '2791')
(SELECT AVG(rating)
FROM hotelreviews WHERE hotel_id = hotels.id
WHERE (rating IS NOT NULL AND rating > 0)
GROUP BY hotel_id) AS avgrating)
FROM hotels
WHERE (id = '2791')
Vergat je daar niet gewoon een haakje-sluit?
Ik heb even een testje gedaan, een join is inderdaad sneller, wel 0,00006 seconden (getest op 1000 queries). Ik zie geen reden om het anders te doen.
In dit geval groepeer je in je subquery op het hotel_id dat in dit geval uniek zal zijn. Maar zodra je gaat groeperen op kolommen waarvan niet alle waarden uniek zijn en je in de SELECT clausule van je hoofdquery kolommen selecteert die de groepen uit je subquery verder onderverdelen, zijn de waarden die je met je subquery selecteert incorrect en niet relevant. Kortom, je zult er altijd zelf voor moeten zorgen dat de GROUP BY clausules kloppen met alle SELECT clausules. En dat vergroot de kans op bugs of incorrecte gegevens aanzienlijk...
Heb gelijk nog iets vreemds met deze query:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT regions.id AS id, regions.name AS state, countries.name AS country, COUNT(hotelreviews.id) AS num
FROM countries
LEFT JOIN regions ON countries.id = regions.country_id
LEFT JOIN cities ON regions.id = cities.region_id
LEFT JOIN hotels ON hotels.city_id = cities.id
LEFT JOIN hotelreviews ON hotelreviews.hotel_id = hotels.id
WHERE countries.code IN ('USA', 'CAN') AND (hotelreviews.approved IS NULL OR hotelreviews.approved = 1)
GROUP BY countries.id, regions.id, regions.name, countries.name
ORDER BY countries.name ASC, regions.name ASC
FROM countries
LEFT JOIN regions ON countries.id = regions.country_id
LEFT JOIN cities ON regions.id = cities.region_id
LEFT JOIN hotels ON hotels.city_id = cities.id
LEFT JOIN hotelreviews ON hotelreviews.hotel_id = hotels.id
WHERE countries.code IN ('USA', 'CAN') AND (hotelreviews.approved IS NULL OR hotelreviews.approved = 1)
GROUP BY countries.id, regions.id, regions.name, countries.name
ORDER BY countries.name ASC, regions.name ASC
Het doel is om alle regios te tonen met het aantal beschikbare reviews. Echter wordt hierbij of 0, 2 of hoger getoond. Dit is vreemd omdat sommige hotels maar 1 review hebben.
Snapt iemand waarom COUNT() dit doet. Ik heb deze bewust op de kolom hotelreviews.id gezet, COUNT() telt alleen wanneer geen NULL
Wederom iets stoms wat ik niet had gezien. Er waren wel twee reviews, echter op een ander scherm werden deze niet getoond omdat ik daar een voorwaarde had, die ik niet in deze query meenam.