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

You have a much better chance of getting a good answer if you put your SQL into Sqlfiddle.com Both the structure and some example data, as well as the expected results. — Brian Hoover, Mar 16 '18 at 03:32
@BrianHoover you can look it [here](http://sqlfiddle.com/#!9/59132/2) but the results are correct as I have added only the `pol_type` part. Sqlfiddle is not allowing me to extend the schema as it gives me an error. — Moeez, Mar 16 '18 at 03:52
Without seeing all the data my assumption would be that the INNER JOINs in your table are creating multiple rows for each survey so that you are counting pole_type more times than you expect. The fact that you have to do a COUNT DISTINCT to get the correct number of surveys would seem to indicate that is the case. — Nick, Mar 16 '18 at 04:28
Yes I have also tried with `COUNT(DISTINCT CASE WHEN sur.`pole_type` LIKE '%Wall%' THEN 1 ELSE NULL END) AS 'Wall'` but still it's not giving me correct result — Moeez, Mar 16 '18 at 04:36
The easiest solution to your problem is probably to put the COUNTS into separate queries (one for the COUNTS on sn, and one for the COUNTS on sur) without the JOINS. — Nick, Mar 16 '18 at 04:52

D-Shih · Answer 1 · 2018-03-16T05:42:22.507

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

Tried with your suggestion, but still, i am getting incorrect count — Moeez, Mar 16 '18 at 05:26
@MrFaisal Is the link http://sqlfiddle.com/#!9/59132/16 with your sample data correct? — D-Shih, Mar 16 '18 at 05:33
I guess the problem is on `Group by` filed you can try my edit answer. — D-Shih, Mar 16 '18 at 05:42
Could you provide `survey` ,`survey_hesco_subdivision` ,`survey_networks` table's schema and few sample data? because there ara join table. — D-Shih, Mar 16 '18 at 06:09
Just need to provide a little bit of information and data tables to put in sqlfiddle, it will be more than 8000 characters? — D-Shih, Mar 16 '18 at 07:14

score 0 · Accepted Answer · answered Mar 16 '18 at 11:04

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.

Count with case not giving correct results

2 Answers2