SQL Sum of Counts and Group By

user_16309932 0
Viewed 153

I have a table with 2 columns:

ID  Status
--  -------
1   OK
2   OK
3   NOTOK
4   NOTOK
5   OK

I want to do a count and group by status and also have the sum of the count on each row. The count works but I do not know how to do the sum to appear on each row like this:

Status     Count   TotalCount
-------  -----  ------
OK        3       5
NOTOK     2       5
sql
4 Answers
select distinct Status, count(*) occurrences, count(*) over(partition by Status) total_count from table group by Status
user_40011770

SELECT COUNT(*) AS Count, SUM(*) AS Sum FROM *your table* GROUP BY Status. You can use AS to rename whatever you want. I think sum will appear now.

user_168437650

You can use two windowed counts:

select distinct status, 
    Count(*) over(partition by status) as "count", 
    Count(*) over() as "Sum"
from t;

without window functions you could take various approaches, one would be

with c as (
    select  status, Count(*) as "Count"
    from t
    group by status
)
select *, (select Sum("count") from c) as "Sum"
from c

And in case CTE's are not supported...

select *, (select Count(*) from t) as "Sum"
from (
    select  status, Count(*) as "Count"
    from t
    group by status
)c
user_153326500

Use a subquery.

SELECT Status, COUNT(*) as Count, (SELECT COUNT(*) FROM my_table) AS TotalCount FROM my_table GROUP BY Status;

SQL Fiddle

user_32629900
Related