ansaurus

Question

How do I count MySQL entries with a HAVING criterion

Answer 1

A:

DISCLAIMER - I've only tested this on SQL Server

HAVING in this case will only perform any aggregate queries over the entire returned set. First of all, you can't run

SELECT COUNT(field.id), field.id * 10 AS foo FROM table HAVING foo > 100

because field.id is not contained in a clause that defines a group or an aggregate function; it just doesn't compile.

With that said, the following SQL -

SELECT COUNT(field.id) FROM table HAVING COUNT(field.id) > 100

will return the count of rows in the table if the count is greater than 100. If it's not, you'll get no result.

Do you have a specific problem in mind? What are you trying to count?

arootbeer 2010-09-20 01:33:31

Answer 2

+1 A:

I can't run either query as-is - they give me a 1140 error for "using an aggregate without a GROUP BY clause" (IE: COUNT(field.id)). Everything appears not to relate to the aggregate at all, just the ability to reference the column alias for comparison...

The most widely supported means is:

SELECT field.id * 10 AS foo 
  FROM table 
 WHERE field.id * 10 > 100

MySQL does support referencing a column alias in the GROUP BY or HAVING clause. It doesn't require using backticks, but I have seen instances that wouldn't work (non-reserved words) until backticks were present:

SELECT field.id * 10 AS foo 
  FROM table 
HAVING `foo` > 100

I don't recommend this approach - it's supported on SQL Server, but not Oracle...

The HAVING clause is like the WHERE clause, the difference is that the HAVING clause supports aggregate functions without needing them to be wrapped in a subquery.

OMG Ponies 2010-09-20 01:33:36

@Martin Smith: Could be, but there was a question recently that wouldn't work unless the alias was in backticks so I'll err to the side of caution.

OMG Ponies 2010-09-20 01:42:48

btw, your second query will always return empty set, because of `HAVING`

zerkms 2010-09-20 01:43:21

@zerkms: Both are equivalent

OMG Ponies 2010-09-20 01:44:13

and to be clear, the **main** difference between `WHERE` and `HAVING` is that `WHERE` works on the physical data level, it works with raw data, and `HAVING` works with complete result set.

zerkms 2010-09-20 01:46:00

@OMG - If I try `SELECT count(id), id+1 as foo FROM mytable` it just returns a single row result set which seems to have the top row in the table. So I guess unless that `id` in the single row happens to be >99 then nothing will be returned.

Martin Smith 2010-09-20 01:46:52

@OMG Ponies: nope, they are not.

zerkms 2010-09-20 01:47:49

@OMG Ponies: create table with one column `a` and add 3 records: 1, 2, 3. `WHERE a > 1` vs `HAVING` equivalent and `WHERE a > 2` vs `HAVING` one will return different results.

zerkms 2010-09-20 01:49:14

@OMG Ponies: omg, how is that related to OP? your second query is wrong...

zerkms 2010-09-20 01:50:35

@zerkms: I tested using a table with 105 records, numbered 1-105. After omitting the COUNT in the original query due to the MySQL 1140 error, I get identical result sets from both versions of the queries I posted: 95 records, with a `foo` value of 100 to 1050, incrementing by 10.

OMG Ponies 2010-09-20 02:08:55

@OMG Ponies: it is impossible. for values more than first row in a table (which is selected when we write `COUNT(*), foo`) `HAVING` should return empty set. And it does on my mysql 5.1. "After omitting the COUNT" --- yes, because it is the key reason of why you get empty set otherwise.

zerkms 2010-09-20 02:20:29

@zerkms: if you tested it, then you'd know MySQL would return a 1140 error for trying to use an aggregate without a group by clause. That's not an empty result set...

OMG Ponies 2010-09-20 02:29:35

@OMG- I don't get this error either. I Think it might be dependant on [this sql mode option](http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_only_full_group_by)?

Martin Smith 2010-09-20 02:39:13

i tested it and it works for me. seems like you have changed some server mode options.

zerkms 2010-09-20 02:42:31

Answer 3

+4 A:

Well, COUNT works BEFORE HAVING is applied to the result set. So if you need to count their number - you have to wrap your query with another one.

SELECT COUNT(*) FROM (
    SELECT field.id * 10 AS foo FROM table HAVING foo > 100
)

zerkms 2010-09-20 01:35:56

this is great, although I had to add turn the query to SELECT COUNT(*) FROM ( SELECT field.id * 10 AS foo FROM table HAVING foo > 100) AS derived

kamikaze_pilot 2010-09-20 02:14:48

@kamikaze_pilot: yup, i forgot that in this case we should give an alias for nested query.

zerkms 2010-09-20 02:22:09

ansaurus

tags:

views:

answers:

How do I count MySQL entries with a HAVING criterion

related questions