How to return one single row from groupped by columns

Question

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

Rich Benner · Accepted Answer · 2016-05-06 10:41:17Z

6

SELECT TIN, 
SUM(case when Specified=0 then 1 else 0 end) as ZeroSpecified,
SUM(case when Specified<>0 then 1 else 0 end) as NOTZEROSpecified
FROM table
GROUP BY TIN

edited May 6, 2016 at 10:41

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

answered May 6, 2016 at 9:32

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nursultan Aidarkulov Over a year ago

Thank you my friend! i forgot about sum function)

Rich Benner Over a year ago

You need to edit the second case, this isn't correct; 'Specified<>1'

Rich Benner · Accepted Answer · 2016-05-06 09:32:38Z

1

Pretty Simple;

SELECT
TIN
,SUM(CASE WHEN Specified = 0 THEN 1 ELSE 0 END) ZEROSpecified
,SUM(CASE WHEN Specified <> 0 THEN 1 ELSE 0 END) NotZEROSpecified
FROM TableName
GROUP BY TIN

answered May 6, 2016 at 9:32

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

Collectives™ on Stack Overflow

How to return one single row from groupped by columns

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related