SUM of multiple rows based on ranking

September 9, 2024

I have a db like so with thousands of job numbers and the workers on each job:

JOB_NUMBER	RANK	EMPLOYEE
1	MGR	DAVE
1	MGR	JOHN
1	JR	TJ
1	JR	FRANK
1	JR	RACHEL
1	JR	GRACE

I want to show a count of how many managers, and how many JR’s there are on each job number.

JOB_NUMBER	RANK	EMPLOYEE	RANK_COUNT
1	MGR	DAVE	2
1	MGR	JOHN	2
1	JR	TJ	4
1	JR	FRANK	4
1	JR	RACHEL	4
1	JR	GRACE	4

At the minute I am using this line as I am unsure how to count the different RANK values in one column:

SUM(CASE WHEN RANK = 'MGR' THEN 1 ELSE 0 END) AS MGR_Count,
SUM(CASE WHEN RANK = 'JR' THEN 1 ELSE 0 END) AS JR_Count,

This gives me a count in two separate columns but also just a single count:

JOB_NUMBER	RANK	EMPLOYEE	MGR_COUNT	JR_COUNT
1	MGR	DAVE	1	0
1	MGR	JOHN	1	0
1	JR	TJ	0	1
1	JR	FRANK	0	1
1	JR	RACHEL	0	1
1	JR	GRACE	0	1

What changes do I need to make to the code in order to present a count of MGR and JR both in one column?

>Solution :

You could use the window variant of count:

SELECT *, COUNT(*) OVER (PARTITION BY [rank])
FROM   [mytable]

EDIT:
To answer the question in the comments, if you want to perform some "aggregation" between different ranks, you could partition by a case expression. E.g.:

SELECT *, COUNT(*) OVER (PARTITION BY CASE [rank]
                                      WHEN 'JR' THEN 'JR_SR'
                                      WHEN 'SR' THEN 'JR_SR'
                                      ELSE [rank]
                                      END)
FROM   [mytable]