[mysql] GROUP BY & WHERE foutje?

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Storeman storeman

storeman storeman

20/06/2009 11:49:00
Quote Anchor link
Ik heb een query waarmee ik een gemiddeld cijfer wil bepalen van een hotel.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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')


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)
PHP script in nieuw venster Selecteer het PHP script
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
 
PHP hulp

PHP hulp

26/01/2020 10:38:53
 
Tikkes C

Tikkes C

20/06/2009 11:54:00
Quote Anchor link
2 maal WHERE...gebruik AND ipv de 2de WHERE
 
Bastiaan

Bastiaan

20/06/2009 12:01:00
Quote Anchor link
Waarom gebruik je niet gewoon een join om dit uit te voeren?

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 C

Tikkes C

20/06/2009 12:05:00
Quote Anchor link
gaat ook inderdaad...nog een opmerking: doe die backticks ook eens weg!
 
Storeman storeman

storeman storeman

20/06/2009 12:32:00
Quote Anchor link
pff, wat stom zeg! Heb ik gewoon helemaal overheen gekeken. Sorry voor deze vervuiling. Ik dacht dat het aan MySql lag, maar dat valt weer mee.

@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
 
Joren de Wit

Joren de Wit

20/06/2009 12:40:00
Quote Anchor link
storeman schreef op 20.06.2009 12:32:
maar heb je een argument waarom dat beter zou zijn?
Ja, omdat het vele malen sneller is dan voor elk record een aparte subquery uit te voeren om het gemiddelde te bepalen.

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)
PHP script in nieuw venster Selecteer het PHP script
1
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
 
Bastiaan

Bastiaan

20/06/2009 12:54:00
Quote Anchor link
Sja is een lastige.

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
 
Bastiaan

Bastiaan

20/06/2009 12:57:00
Quote Anchor link
mm, had je post nog niet gezien blanche..
 
Jesper Diovo

Jesper Diovo

20/06/2009 13:06:00
Quote Anchor link
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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')

Vergat je daar niet gewoon een haakje-sluit?
 
Storeman storeman

storeman storeman

20/06/2009 13:14:00
Quote Anchor link
@Blanche: Ja bij meerdere rijen is dat natuurlijk logisch, maar het gaat mij hier om één hotel, dus 1 rij. (een detail pagina). Bij lijsten gebruik in inderdaad een join om het gemiddelde te bepalen.

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.
 
Joren de Wit

Joren de Wit

20/06/2009 13:43:00
Quote Anchor link
Bij zo weinig records zal het snelheidsverschil inderdaad niet merkbaar zijn. Maar er is nog een andere belangrijke reden waarom ik het niet met een subquery zou aanpakken en dat is de integriteit van je query.

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...
 
Storeman storeman

storeman storeman

20/06/2009 14:34:00
Quote Anchor link
@Blanche, oke, je hebt me overtuigd.

Heb gelijk nog iets vreemds met deze query:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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


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
 
Joren de Wit

Joren de Wit

20/06/2009 14:43:00
Quote Anchor link
Zijn de hogere waarden ook allen een veelvoud van 2?
 
Storeman storeman

storeman storeman

20/06/2009 15:19:00
Quote Anchor link
Hmm, ik moet maar niet werken op zaterdag.

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.
 
Joren de Wit

Joren de Wit

20/06/2009 15:30:00
Quote Anchor link
Ik kon al niets vinden in die query :)
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.