CODEWITHSUNDEEP
mysql
user1218172
user1218172

Reputation: 193

Trying to exclude unwanted GROUP BY categories in MySQL query

I am able to run the following query just fine, except the results are returning empty rows for 'Archived' and 'Rejected' (which are the other two status's in the status column) which I wish to exclude from the results

SELECT status,
sum(case when status = 'New' then 1 else 0 end),
sum(case when status = 'Active' then 1 else 0 end),
sum(case when status = 'Closed' then 1 else 0 end)
FROM Contact
GROUP BY status

Right now the query returns:

0             1   2   3
Archived      0   0   0
Rejected      0   0   0
New           5   0   0
Active        0   4   0
Closed        0   0   7

I am trying to get it to return:

0             1   2   3
New           5   0   0
Active        0   4   0
Closed        0   0   7

Any help to point me in the right direction would be appreciated.

Upvotes: 0

Views: 315

Answers (3)

Elvin
Elvin

Reputation: 2291

You only need to add having clause

SELECT status,
sum(case when status = 'New' then 1 else 0 end),
sum(case when status = 'Active' then 1 else 0 end),
sum(case when status = 'Closed' then 1 else 0 end)
FROM Contact
GROUP BY status
Having status NOT IN('Archieved', 'Rejected');


For better understanding about it just see the use of Having

Upvotes: 1

Nadeem_MK
Nadeem_MK

Reputation: 7689

Try excluding them literally from your select;

SELECT status,
sum(case when status = 'New' then 1 else 0 end),
sum(case when status = 'Active' then 1 else 0 end),
sum(case when status = 'Closed' then 1 else 0 end)
FROM Contact
WHERE Status <> 'Archived'
AND   Status <> 'Rejected'
GROUP BY status

Upvotes: 3

har07
har07

Reputation: 89295

Add : HAVING status NOT IN('Archieved', 'Rejected') after GROUP BY clause.

Upvotes: 4

Related Questions