Count with case not giving correct results

Question

I am using a query to generate a count. Below is my query

SELECT COUNT(DISTINCT sur.`customer_id`) AS 'Survey Done'
 ,COUNT(CASE WHEN sn.operator_name LIKE '%Zong%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE NULL END) AS 'Zong No Signal'
 ,COUNT(CASE WHEN sn.operator_name LIKE '%Mobilink%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE NULL END) AS 'Mobilink No Signal'
 ,COUNT(CASE WHEN sn.operator_name LIKE '%Ufone%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE NULL END) AS 'Ufone No Signal'
 ,COUNT(CASE WHEN sn.operator_name LIKE '%Telenor%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE NULL END) AS 'Telenor No Signal'
 ,COUNT(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE NULL END) AS 'Wall'
 ,COUNT(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE NULL END) AS 'PC Pole'
 ,COUNT(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE NULL END) AS 'Structure pole'
 ,COUNT(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE NULL END) AS 'Spon pole'
 ,sd.`sub_div_code` AS 'SD Code',  
 sd.`name` AS 'SD Name', 
 sd.`circle_name` AS 'Circle Name', 
 sd.`division_name` AS 'Division Name'
 FROM `survey` sur 
 INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = 
 sd.`sub_div_code`
 INNER JOIN `survey_networks` sn ON sur.`id` = sn.`survey_id`
 WHERE sur.`customer_id` IN ('37010185878',
'37010718785',
'37010718759',
'37010357911',
'37010673539',
'37010673796',
'37010672166',
'37010672162')
 GROUP BY sd.`name`

All the counts are correct but for the below part the values are incorrect

,COUNT(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE NULL END) AS 'Wall'
,COUNT(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE NULL END) AS 'PC Pole'
,COUNT(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE NULL END) AS 'Structure pole'
,COUNT(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE NULL END) AS 'Spon pole'

The output for them is 10,4,24 and 0. But the actual count is 4,1,7 and 0

The sample output is

The last value spon pole is 0 for some records but not for all, hence it's count is also not correct.

How can I get the correct count of these values? I have also tried = sign in replace of LIKE but still it won't gives me the correct result. I have also seen this solution

Any help would be highly appreciated

Answer 1

So, after a lot of searching, I am able to figure out the correct query which is giving me correct results

SELECT SUM(z.Survey_Done) AS 'Survey Done',SUM(Zong) AS 'Zong No Signal',SUM(Mobilink) AS 'Mobilink No Signal',SUM(Ufone) AS 'Ufone No Signal',SUM(Telenor) AS 'Telenor No Signal'
,SUM(Wall) AS Wall,SUM(PC_Pole) AS 'PC Pole',SUM(Structure_pole) AS 'Structure Pole',SUM(Spon_pole) AS 'Spon Pole',SDCode
,sd.`name` AS 'SD Name' 
,sd.`circle_name` AS 'Circle Name'
,sd.`division_name` AS 'Division Name'
FROM (
SELECT COUNT(DISTINCT sur.`customer_id`) AS 'Survey_Done',
0 AS 'Zong',
0 AS 'Mobilink',
0 AS 'Ufone',
0 AS 'Telenor'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE 0 END) AS 'Wall'
,SUM(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE 0 END) AS 'PC_Pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE 0 END) AS 'Structure_pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE 0 END) AS 'Spon_pole'
,sd.`sub_div_code` AS 'SDCode'

FROM `survey` sur 
INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = 
sd.`sub_div_code`

WHERE sur.`customer_id` IN ()
GROUP BY sd.`sub_div_code`, sd.`name`, sd.`circle_name`, sd.`division_name`
UNION

SELECT 
0 AS 'Survey_Done',
SUM(CASE WHEN sn.operator_name LIKE '%Zong%' AND sn.`signal_strength` = 'No 
Signal' THEN 1 ELSE 0 END) AS 'Zong'
,SUM(CASE WHEN sn.operator_name LIKE '%Mobilink%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Mobilink'
,SUM(CASE WHEN sn.operator_name LIKE '%Ufone%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Ufone'
,SUM(CASE WHEN sn.operator_name LIKE '%Telenor%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Telenor'
,0 AS 'Wall'
,0 AS 'PC Pole'
,0 AS 'Structure pole'
,0 AS 'Spon pole'
,sd.`sub_div_code` AS 'SDCode'
FROM  `survey_networks` sn 
INNER JOIN `survey` sur ON sur.`id` = sn.`survey_id`
INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = 
sd.`sub_div_code`

WHERE sur.`customer_id` IN ()
GROUP BY sd.`sub_div_code`
) z
INNER JOIN `survey_hesco_subdivision` sd ON sd.`sub_div_code`=SDCode
GROUP BY sd.`name`

I have used UNION in the above query.

Answer 2

You might use SUM instead of Count and ELSE set 0

SELECT COUNT(DISTINCT sur.`customer_id`) AS 'Survey Done'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE 0 END) AS 'Wall'
,SUM(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE 0 END) AS 'PC Pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE 0 END) AS 'Structure pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE 0 END) AS 'Spon pole'
,sur.`sub_division`
FROM `survey` sur 

if you want to distinguish on sur.sub_division just add group by by sur.sub_division

SELECT COUNT(DISTINCT sur.`customer_id`) AS 'Survey Done'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE 0 END) AS 'Wall'
,SUM(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE 0 END) AS 'PC Pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE 0 END) AS 'Structure pole'
,SUM(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE 0 END) AS 'Spon pole'
,sur.`sub_division`
FROM `survey` sur 
GROUP BY sur.`sub_division`

Edit

I guess the problem is on Group by filed you can try this.

SELECT COUNT(DISTINCT sur.`customer_id`) AS 'Survey Done'
     ,SUM(CASE WHEN sn.operator_name LIKE '%Zong%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Zong No Signal'
     ,SUM(CASE WHEN sn.operator_name LIKE '%Mobilink%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Mobilink No Signal'
     ,SUM(CASE WHEN sn.operator_name LIKE '%Ufone%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Ufone No Signal'
     ,SUM(CASE WHEN sn.operator_name LIKE '%Telenor%' AND sn.`signal_strength` = 'No Signal' THEN 1 ELSE 0 END) AS 'Telenor No Signal'
     ,SUM(CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE 0 END) AS 'Wall'
     ,SUM(CASE WHEN sur.`pole_type` LIKE '%PC Pole%' THEN 1 ELSE 0 END) AS 'PC Pole'
     ,SUM(CASE WHEN sur.`pole_type` LIKE '%Structure Pole%' THEN 1 ELSE 0 END) AS 'Structure pole'
     ,SUM(CASE WHEN sur.`pole_type` LIKE '%Spon pole%' THEN 1 ELSE 0 END) AS 'Spon pole'
     ,sd.`sub_div_code` AS 'SD Code',  
     sd.`name` AS 'SD Name', 
     sd.`circle_name` AS 'Circle Name', 
     sd.`division_name` AS 'Division Name'
 FROM `survey` sur 
 INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = 
 sd.`sub_div_code`
 INNER JOIN `survey_networks` sn ON sur.`id` = sn.`survey_id`
 WHERE sur.`customer_id` IN 
 ('37010185878',
'37010718785',
'37010718759',
'37010357911',
'37010673539',
'37010673796',
'37010672166',
'37010672162')
 GROUP BY 
     sd.`sub_div_code`, 
     sd.`name`,
     sd.`circle_name`, 
     sd.`division_name`

SQLFiddle