CODEWITHSUNDEEP
sqlsql-server
user8260195
user8260195

Reputation:

How to get grouped output for a particular column as shown below in SQL?

There is a table with two columns and this query:

select SupplierName, Acknowledge 
from tbPOValidation

returns this result set:

SupplierName         Acknowledge
--------------------------------
SUPPLIER COMPANY        1
CANADA SUPPLIERS        0
MOTION INDUSTRIES       0
SUPPLIER COMPANY        1
CANADA SUPPLIERS        0
INDUSTRY CORP           1
CORP SUPPLIERS          1
JAMES SUPPLIERS         NULL
MOTION INDUSTRIES       NULL

I want the output to look like this (ack 1 = yes, 0 = no, Null= Null)

SupplierName      YES   NO  NULL
--------------------------------
JAMES SUPPLIERS   0     0   1
INDUSTRY CORP     1     0   0
CANADA SUPPLIERS  0     2   0
MOTION INDUSTRIES 0     1   1
SUPPLIER COMPANY  2     0   0
CORP SUPPLIERS    1     0   0

How can I get this?

Upvotes: 2

Views: 47

Answers (1)

Madhukar
Madhukar

Reputation: 1242

Below SQL would help you.

SELECT SupplierName, 
    SUM(CASE WHEN Acknowledge = 1 THEN 1 ELSE 0 END) AS 'YES',
    SUM(CASE WHEN Acknowledge = 0 THEN 1 ELSE 0 END) AS 'NO',
    SUM(CASE WHEN Acknowledge IS NULL THEN 1 ELSE 0 END) AS 'NULL' 
FROM tbpovalidation
GROUP BY SupplierName

Upvotes: 4

Related Questions