CODEWITHSUNDEEP
mysql
user1218172
user1218172

Reputation: 193

Including results in GROUP BY when the count is zero for one of the groups

I am successfully running the following query, but need to all status's represented in the returned results, even if there are zero status's of one kind or another:

SELECT status,
sum(case when status = 'New' then 1 else 0 end),
sum(case when status = '1 Attempt' then 1 else 0 end),
sum(case when status = '2 Attempts' then 1 else 0 end),
sum(case when status = '3 Attempts' then 1 else 0 end),
sum(case when status = 'Connected' then 1 else 0 end),
sum(case when status = 'Follow Up' then 1 else 0 end),
sum(case when status = 'Referred' then 1 else 0 end)
FROM Contact
GROUP BY status
HAVING status NOT IN('Invalid', 'Archived')
ORDER BY FIELD (status, 'New', '1 Attempt', '2 Attempts', '3 Attempts', 'Connected',    'Follow Up', 'Referred')

Presently, I get these results if there is 1 "New" status and 1 "Connected" status:

0          1 2 3 4 5 6 7
New        1 0 0 0 0 0 0
Connected  0 0 0 0 1 0 0

What I want is in the case above is:

0          1 2 3 4 5 6 7
New        1 0 0 0 0 0 0
1 Attempt  0 0 0 0 0 0 0
2 Attempts 0 0 0 0 0 0 0
3 Attempts 0 0 0 0 0 0 0
Connected  0 0 0 0 1 0 0
etc...

Any help to do this would be much appreciated. Thanks!

Upvotes: 1

Views: 139

Answers (3)

krokodilko
krokodilko

Reputation: 36107

Try:

SELECT p.status as `0`,
sum(case when c.status = 'New' then 1 else 0 end) as `1`,
sum(case when c.status = '1 Attempt' then 1 else 0 end) `2`,
sum(case when c.status = '2 Attempts' then 1 else 0 end) `3`,
sum(case when c.status = '3 Attempts' then 1 else 0 end) `4`,
sum(case when c.status = 'Connected' then 1 else 0 end) `5`,
sum(case when c.status = 'Follow Up' then 1 else 0 end) `6`,
sum(case when c.status = 'Referred' then 1 else 0 end) `7`
FROM (
  SELECT 'New' Status
  Union SELECT '1 Attempt' Status
  Union SELECT '2 Attempts' Status
  Union SELECT '3 Attempts' Status
  Union SELECT 'Connected' Status
  Union SELECT 'Follow Up' Status
  Union SELECT 'Referred' Status
) p
LEFT JOIN Contact c ON p.status = c.status
GROUP BY p.status
HAVING status NOT IN('Invalid', 'Archived')
ORDER BY FIELD (p.status, 'New', '1 Attempt', '2 Attempts', '3 Attempts', 'Connected',    'Follow Up', 'Referred')

Demo --> http://www.sqlfiddle.com/#!2/9500f/1

Upvotes: 1

Wolfgang Stengel
Wolfgang Stengel

Reputation: 2856

This will not be exactly the format you require, but it will fetch the required data in one row, with each sum as a column:

SELECT
SUM(CASE WHEN status = 'New' THEN 1 ELSE 0 END) AS count_new,
SUM(CASE WHEN status = '1 Attempt' THEN 1 ELSE 0 END) AS count_1_attempt,
SUM(CASE WHEN status = '2 Attempts' THEN 1 ELSE 0 END) AS count_2_attempts,
SUM(CASE WHEN status = '3 Attempts' THEN 1 ELSE 0 END) AS count_3_attempts,
SUM(CASE WHEN status = 'Connected' THEN 1 ELSE 0 END) AS count_connected,
SUM(CASE WHEN status = 'Follow Up' THEN 1 ELSE 0 END) AS count_follow_up,
SUM(CASE WHEN status = 'Referred' THEN 1 ELSE 0 END) AS count_referred
FROM Contact

Upvotes: 0

kiks73
kiks73

Reputation: 3758

select status, sum(case when status ='New' then 1 else 0) as '1',0 as '2', 0 as '3', 0 as '4', ..., from Contact group by status 

Union

select status, 0 as '1' , sum(case when status ='1 Attempt' then 1 else 0) as '1',0 as '2', 0 as '3', 0 as '4', ..., from Contact group by status

Union


...

Upvotes: 0

Related Questions