Count only once

1

I have this query that outputs these:
school student program
A 1 prog1
A 1 NULL
A 2 NULL
A 3 NULL
A 4 NULL
A 4 prog1
A 5 prog1
A 5 NULL
A 6 prog2
A 6 NULL

What I want to do is count these students in school A like this:

school prog count
A NULL 2
A prog1 3
A prog2 1

If a student has more than 1 row, the row that counts is the row that has a program. Hence, student 1, 4 & 5 are counted in prog1 and student 6 is counted in prog 2. A student with one row will always have program as NULL.

Thanks for your help.

2

No usable data to test with, but I'm pretty sure this is it:

SELECT school, program, COUNT(*) AS count
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY school, student ORDER BY program DESC) AS row_num
    FROM dbo.table_name
) AS qry1
WHERE row_num = 1
GROUP BY school, program
ORDER BY school, program
1 Like
3

Nice! Thanks for the tip!