CODEWITHSUNDEEP
sqlsql-server
Andrei Drynov
Andrei Drynov

Reputation: 8592

Aggregate list of users and values into a table with list of users and counts of values

I have a table of users with three columns.

Username    Accepted    Rejected 

User 1      1            NULL 
User 1      1            NULL 
User 1      NULL         1
User 2      1            1
User 3      1            NULL 
User 3      1            NULL 
User 2      NULL         1
User 3      NULL         NULL 
User 2      NULL         NULL

I'd like to show a list of all the users with counts of Accepted/Rejected columns like so:

Username    Accepted    Rejected 
User 1      2           1
User 2      1           2
User 3      2           NULL

What is the best way to do it?

Upvotes: 1

Views: 62

Answers (2)

user1310114
user1310114

Reputation:

SELECT u.username, CASE WHEN COUNT(u.accepted) = 0 THEN NULL ELSE COUNT(u.accepted) END AS Accepted, CASE WHEN COUNT(u.rejected) = 0 THEN NULL ELSE COUNT(u.rejected) END AS Rejected FROM usr1 u

GROUP BY u.username

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

If Accepted and Rejected can contain 1, 0 and null only use sum because sum will return null if all values supplied are null:

select UserName, 
       sum(Accepted) Accepted,
       sum(Rejected) Rejected
  from ATable
 group by UserName

Count will return zero in Rejected column of user3.

Upvotes: 2

Related Questions