mysql query for getting count by group and total count
mysql, sql
Solution
the optimized way is to first calculate the count and then simply put the variable in your select statement:
set @rowCount = (select count(col1) from table1);
select col1, count(col1), @rowCount from table1 group by col1;
See the result
The approach given by @Meherzad will calculate the row count many times. But if you want to do this in a single query u can use:
`select col1, count(col1), (select count(col1) from table1) rowCount from table1 group by col1;`
Problem
I have a table 'table1' as follows: ``` col1 ---- 1 1 2 2 2 3 3 ``` I want to get count by group and total count from this table as follows: ``` col1 group_count total_count ------------------------------------- 1 2 7 2 3 7 3 2 7 ``` I tried as follows: ``` SELECT col1, group_count, total_count FROM (SELECT col1, COUNT(col1) AS group_count FROM table1 GROUP BY col1) Temp1, (SELECT COUNT(col1) AS total_count FROM table1) Temp2 ``` How to do it in optimised way