Nursultan Aidarkulov Nursultan Aidarkulov - 7 months ago 9
SQL Question

How to return one single row from groupped by columns

I have table like this

ID Specified TIN
-----------------
1 0 tin1
2 0 tin1
3 1 tin1
4 0 tin2
5 0 tin3
6 1 tin3
7 1 tin3


I need to count rows groupped by TIN, Specified columns - but result should one row for each TIN:

TIN ZEROSpecified NOTZEROSpecified
tin1 2 1
tin2 0 1
tin3 1 2


Important notice - i have only 2 values for Specified column - 0 and 1

Answer
select TIN, 
sum(case when Specified=0 then 1 else 0 end) as ZeroSpecified,
sum(case when Specified<>1 then 1 else 0 end) as NOTZEROSpecified
from table
group by TIN