Mysql Query format question

egan007 · 05-12-2007 10:23AM #1

I'm using this query to get the average mark for each pupil id.

SELECT AVG( mark) , COUNT( mark) , id
FROM school
WHERE class= '4'
GROUP BY id
LIMIT 0 , 10

This works fine.

Now I want to add a condition based on the average mark and the count..

Like this

SELECT AVG( mark) , COUNT( mark) , id
FROM school
WHERE class= '4'
AND AVG( mark) > '50'
AND COUNT(mark) > '2'
GROUP BY id
LIMIT 0 , 10

I need help on formatting this statement correctly.

MOH · 05-12-2007 10:30AM

Not sure if MySQL has anything different, but normally:

SELECT AVG( mark) , COUNT( mark) , id
FROM school
WHERE class= '4'
GROUP BY id
HAVING AVG( mark) > '50'
AND COUNT(mark) > '2'

egan007 · 05-12-2007 10:38AM

Works a charm, much appreciated.

phil · 05-12-2007 03:48PM

Just an FYI on why (always good to know). Table JOINs and WHERE conditions are applied before aggregate functions (it's slightly reflected in the syntax of the SQL command itself). So the database grabs all of the rows needed, filters out using your where conditions and then performs your AVG() and SUM() agg. functions.

It's impossible therefore to use the results of these AVG() and SUM() functions within a WHERE clause. The SQL HAVING keyword was included exactly for this reason. The filters you use within this structure are applied AFTER your agg. functions.

The one thing you need to be careful of here is that HAVING clauses are not necessarily portable. Row and result aliases as well as fields available may differ between databases (and even between MySQL versions - notably 4 & 5 have differences). So test carefully if this is something that's important to you.

MOH · 05-12-2007 04:17PM

You could also do this:

SELECT a.* FROM
(SELECT AVG(mark) AS avg, COUNT(mark) as counter, id
FROM school
WHERE class = '4'
GROUP BY id) as a
WHERE avg > 50
AND counter > 2

Don't know if that would be any more/less portable, or slower, but it's certainly uglier!

Also, wondering why you hvae quotes around the numbers?

Mysql Query format question

Comments