Menu
I have a mysql select query that has a groupBy. I want to count all the records after the group by statement. Is there a way for this directly from mysql ?
thanks.
309
After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause. It's always used after the GROUP BY clause. In HAVING, we use a condition to compare a value with one returned by the aggregate function. In the example, we compare whether COUNT(id) returns a value higher than two.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
SQL – count() with Group By clauseThe count() function with the GROUP BY clause is used to count the data which were grouped on a particular attribute of the table.
After Grouping the data, you can filter the grouped record using HAVING Clause. HAVING Clause returns the grouped records which match the given condition. You can also sort the grouped records using ORDER BY. ORDER BY used after GROUP BY on aggregated column.
If the only thing you need is the count after grouping, and you don't want to use 2 separate queries to find the answer. You can do it with a sub query like so...
select count(*) as `count`
from (
select 0 as `doesn't matter`
from `your_table` yt
group by yt.groupfield
) sq
Note: You have to actually select something in the sub query, but what you select doesn't matter
Note: All temporary tables have to have a named alias, hence the "sq" at the end
180
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
