mysql query for getting count by group and total count

Question

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

Answer 1

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;

Answer 2

Try this query

select col1, count(*), tot 
from tbl t1, (select count(*) as tot from tbl) t2 
group by col1

Fiddle