Tricky SQL Count (I think)

Question

Been trying all the examples and help from others that people have been posting.

Can't get it too work.

Basically I have

These fields and variables

[REF], [STATUS], [DATE], [Manager]
1       Review    121220   George
2       Closed    121220   George
2       Closed    122200   George

I want the output to give me the reference # and the amount of times it was closed so I can join onto another dataset.

so

REF TimesClosed
1       NULL/0 
2       2

Is this possible?

Can you show us what you've done so far? Stack Overflow is not a free code writing service. You are expected to try to write the code yourself. — Roman Marusyk, Apr 10 '18 at 10:57
"Can't get it too work" is not a problem description. Show what you tried, and explain why it didn't work and/or what errors you got. Also, [edit] your post to tag which SQL implementation you are using; while it may not matter much in this case, it's good practice. — underscore_d, Apr 10 '18 at 11:00

score 3 · Answer 1 · answered Apr 10 '18 at 10:58

3

This is only tricky because you cannot filter in the WHERE clause -- otherwise the row you want would not be in the result set.

So, use conditional aggregation:

select ref, sum(case when status = 'Closed' then 1 else 0 end) as cnt
from t
group by ref;

answered Apr 10 '18 at 10:58

Gordon Linoff

score 2 · Answer 2 · answered Apr 10 '18 at 11:00

2

If you only need a summary of closures, then you might be able to avoid using a CASE expression, e.g.

SELECT ref, COUNT(*) AS closed_cnt
FROM yourTable
WHERE [STATUS] = 'Closed'
GROUP BY ref;

answered Apr 10 '18 at 11:00

Tim Biegeleisen

score 1 · Answer 3 · answered Apr 10 '18 at 10:58

1

This would be go with case expression with sum()

select REF, sum(case when STATUS = 'Closed' then 1 else 0 end) TimesClosed
from table t
group by REF

answered Apr 10 '18 at 10:58

Yogesh Sharma

3 Answers3