WHERE in Aggregate function

Question

I have a quick question. I know that we cannot use WHERE clause in an aggregate function in MySQL. The table structure is as follows:

+----+------+----------+--------+
| ID | Name | Location | Active |
+----+------+----------+--------+
|  1 | Aaaa | India    | 0      |
|  2 | Aaaa | USA      | 0      |
|  3 | Aaaa | USA      | 1      |
|  4 | Aaaa | India    | 0      |
|  5 | Aaaa | UK       | 0      |
|  6 | Aaaa | India    | 1      |
|  7 | Aaaa | USA      | 1      |
|  8 | Aaaa | USA      | 0      |
|  9 | Aaaa | India    | 0      |
| 10 | Aaaa | UK       | 1      |
+----+------+----------+--------+

The query I have here is:

SELECT COUNT(*), `location`, `active` FROM `users` GROUP BY `location`;

The above query will give me the counts of the location. But, I need only the active users. So, I need a WHERE clause that does something like:

SELECT COUNT(*), `location`, `active` FROM `users` GROUP BY `location` WHERE `active`=1;

The above query is invalid. The valid query would be using HAVING. But, if I change the query to:

SELECT COUNT(*), `location`, `active` FROM `users` GROUP BY `location` HAVING `active`=1;

The counts are no different from the original query, which is:

SELECT COUNT(*), `location`, `active` FROM `users` GROUP BY `location`;

So, what am I supposed to do for getting the user counts of the location, who are active? Thanks in advance.

_“The above query is invalid”_ – but only because you messed up the order of WHERE and GROUP BY, I suppose … _“The valid query would be using HAVING”_ – nope, that would _mean_ something completely different. — CBroe, Aug 17 '14 at 14:30

score 2 · Accepted Answer · answered Aug 17 '14 at 14:30

Use where before group by so where clause will filter out the results according to your criteria and you can have your count on basis of your where criteria

SELECT COUNT(*), `location`, `active` 
FROM `users` 
 WHERE `active`=1
GROUP BY `location`

In addition to your specific question you can also use sum with your criteria so this will return as boolean and you can have your count on basis of your expression like

SELECT sum(`active`=1), `location`, `active` 
FROM `users` 
GROUP BY `location`

Above sum expression is equivalent to sum(CASE when active=1 THEN 1 ELSE 0 END)

@PraveenKumar [*`Fiddle Demo`*](http://sqlfiddle.com/#!2/527dd/2) for better understanding :) — M Khalid Junaid, Aug 17 '14 at 14:38

siride · Answer 2 · 2014-08-17T14:40:26.987

You can put the WHERE before the GROUP BY if you want to filter rows before aggregating. I you want to do some calculation among rows already aggregated, you have to use a CASE expression inside the aggregate. I don't think that's what you're doing here, so just put the WHERE clause in the right place.

The query looks like this, with active removed because it's now redundant (will always be 1):

SELECT COUNT(*), location
  FROM users
 WHERE active = 1
 GROUP BY location, active

It's important here to be reminded of the logical order of operations of an SQL query:

FROM: starting table, view, derived table
JOIN: adding additional tables, views, etc.
WHERE: filtering the rows from the initial table and joined tables
GROUP BY: aggregating the rows that have been filtered by WHERE
HAVING: filtering the aggregated rows output by GROUP BY
SELECT: pick columns and compute expressions
ORDER BY: sort the resulting rows, with selected columns and computed expressions available for ordering
LIMIT: control which and how many rows go back to the client

If you keep this in mind, it'll be easier to understand when to do certain types of filtering and what the rows that you're filtering look like.

score 0 · Answer 3 · answered Aug 17 '14 at 14:31

0

You can use where in aggregate queries, however, it must be before group by and after from

answered Aug 17 '14 at 14:31

MrTux

32,350
30
109
146

WHERE in Aggregate function

3 Answers3