Select with count by item and total count in SQL Server

Question

I am using the following procedure to add a count per item which works fine so far.

How do I have to change this if I also want to get the total count in addition so that it counts all items in that select ?

My procedure:

SELECT      RANK() OVER(ORDER BY COUNT(*) desc, policy) [Rank],
            policy, 
            COUNT(*) AS groupCount,
            'currentMonth' AS groupName
FROM        Log_PE 
WHERE       CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), GETDATE(), 112) + '01', 112)
GROUP BY    policy
ORDER BY    groupCount desc, policy

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

Already answered in another post : SUM of grouped COUNT in SQL Query

select name, COUNT(name) as count from Table
group by name
    Union all
select 'SUM', COUNT(name)
from Table

edited May 23 '17 at 12:05

Community

1
1

answered Apr 02 '14 at 12:55

Helodia

108
2
9

Thanks. I already have a Group By for my count per item. How can I use your suggestion in this case ? – user2571510 Apr 02 '14 at 12:58
1

I just tested it, it does not pose any problem. Just put the 'union all' after the 'group by' clause – Helodia Apr 02 '14 at 13:03
just the order by is tricky : http://stackoverflow.com/questions/354224/combining-union-all-and-order-by-in-firebird – Helodia Apr 02 '14 at 13:08

score 0 · Answer 2 · answered Apr 02 '14 at 12:49

0

Take a llok at GROUP BY Modifiers.

The part that might be helpful is WITH ROLLUP.

answered Apr 02 '14 at 12:49

blue

1,939
1
11
8

Wumar · Answer 3 · 2014-04-02T13:13:53.567

You can use With Rollup(As suggested by blue ) as well as Grouping Sets, Grouping sets give you more flexibility-

SELECT      RANK() OVER(ORDER BY COUNT(*) desc, policy) [Rank],
            policy, 
            COUNT(*) AS groupCount,
            'currentMonth' AS groupName
FROM        Log_PE 
WHERE       CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), GETDATE(), 112) + '01', 112)
GROUP BY Grouping Sets (policy,())
ORDER BY    groupCount desc, policy

Select with count by item and total count in SQL Server

3 Answers3