using count() in mySQL and AS

Question

How can I extract the count() as an alias? I've basically got this query?

SELECT *, count(*) AS total_count  
FROM `action_log` 
WHERE `response` LIKE '%media%' 
AND `action_time` > '2014-01-05 21:31:33' 
AND `action_time` < '2014-01-07 12:30:00' 
AND `total_count` > '1'
GROUP BY `username`

The error is shown as below

#1054 - Unknown column 'total_count' in 'where clause'

I understand that syntax isn't correct, I've looked through the mySQL references and I can't seem to find it anywhere?

Are you getting an error with this query? What results do you want to get? — Gordon Linoff, Jan 14 '14 at 14:11
It looks like you want to get rows detail for the time span, plus a count of how many rows you retrieved. Those are two different things, and while there are ways to do both with one query you're better off either (a) doing one query for detail and one for count, (b) doing [something like this](http://stackoverflow.com/a/2229233/2091410), or (c) doing one query for detail and getting the count in your front-end program if you have one. — Ed Gibbs, Jan 14 '14 at 14:17

carexcer · Accepted Answer · 2014-01-14T14:44:21.973

2

Try using having and set on SELECT the same fields that in GROUP BY:

  SELECT username, count(*) as total_count
    FROM action_log 
    WHERE response LIKE '%media%' 
    AND action_time > '2014-01-05 21:31:33' 
    AND action_time < '2014-01-07 12:30:00' 
    GROUP BY username
    HAVING count(*) > 1

edited Jan 14 '14 at 14:44

answered Jan 14 '14 at 14:38

carexcer

1,407
2
15
27

score 2 · Answer 2 · answered Jan 14 '14 at 14:39

I think you should look into the HAVING clause. If you want to filter by the result of an aggregate function such as count(*), you need to use HAVING. Something like:

select some_fields, count(*) total_count
from action_log
where action_time > '2014-01-05 21:31:33' 
      and action_time < '2014-01-07 12:30:00'
group by some_fields
having count(*) > 1

score 0 · Answer 3 · answered Jan 14 '14 at 15:03

You can also use the alias from above for the HAVING clause:

SELECT some_fields, count(*) AS total_count
FROM action_log
WHERE action_time > '2014-01-05 21:31:33' 
      AND action_time < '2014-01-07 12:30:00'
GROUP BY some_fields
HAVING total_count > 1

using count() in mySQL and AS

3 Answers3