SELECT dev_block,
CASE WHEN problem_type = 'Relating to Cultivation' THEN COUNT(dev_block) ELSE 0 END AS RelatingtoCultivation,
CASE WHEN problem_type = 'Relating to storage' THEN COUNT(dev_block) ELSE 0 END AS Relatingtostorage,
CASE WHEN problem_type = 'Relating to transport' THEN COUNT(dev_block) ELSE 0 END AS Relatingtotransport,
CASE WHEN problem_type = 'Relating to marketing' THEN COUNT(dev_block) ELSE 0 END AS Relatingtomarketing,
CASE WHEN problem_type = 'Others' THEN COUNT(dev_block) ELSE 0 END AS OTHERS,
CASE WHEN problem_type = '' THEN COUNT(dev_block) ELSE 0 END AS NoProblem
FROM (SELECT a.dev_block, b.problem_type FROM hd_survey_head a JOIN hd_survey_horti_problems b ON a.survey_id = b.survey_id
WHERE b.problem_head = 'Major Problems being faced for Fruit Crops' AND district = 'SHIMLA') AS z
GROUP BY dev_block
dev_block RelatingtoCultivation Relatingtostorage Relatingtotransport Relatingtomarketing Others NoProblem
-------------- --------------------- ----------------- ------------------- ------------------- ------ -----------
BASANTPUR 0 0 0 19209 0 0
CHAUHARA 11317 0 0 0 0 0
CHAUPAL 121086 0 0 0 0 0
JUBBAL KOTKHAI 94635 0 0 0 0 0
KUPVI 0 0 0 6491 0 0
MASHOBRA 23572 0 0 0 0 0
NANKHARI 28695 0 0 0 0 0
NARKANDA 41885 0 0 0 0 0
RAMPUR 67094 0 0 0 0 0
ROHRU 55563 0 0 0 0 0
THEOG 51964 0 0 0 0 0
TOTU 34950 0 0 0 0 0
The above query gives me this output which is wrong it counts all in just one column what I am trying to do is count the problems which are in the nested query and display it in the column.
dev_block problem_type
--------- -------------------------
RAMPUR Relating to Cultivation
RAMPUR Relating to Cultivation
RAMPUR Relating to Cultivation
RAMPUR Relating to Cultivation
RAMPUR Relating to storage
RAMPUR Relating to storage
RAMPUR Relating to transport
RAMPUR Relating to transport
RAMPUR Relating to marketing
RAMPUR Relating to marketing
RAMPUR Relating to marketing
The above data is the result of the nested query as you can see that RAMPUR Block has problems Relating to marketing, Relating to transport and Relating to storage as well but it counts all in Relating to Cultivation in the above query
CodePudding user response：
You should be taking aggregates of the CASE expressions:
SELECT
dev_block,
SUM(problem_type = 'Relating to Cultivation') AS RelatingtoCultivation,
SUM(problem_type = 'Relating to storage') AS Relatingtostorage,
SUM(problem_type = 'Relating to transport') AS Relatingtotransport,
SUM(problem_type = 'Relating to marketing') AS Relatingtomarketing,
SUM(problem_type = 'Others') AS OTHERS,
SUM(problem_type = '') AS NoProblem
FROM (
SELECT a.dev_block, b.problem_type
FROM hd_survey_head a
INNER JOIN hd_survey_horti_problems b ON a.survey_id = b.survey_id
WHERE b.problem_head = 'Major Problems being faced for Fruit Crops' AND
district = 'SHIMLA'
) AS z
GROUP BY dev_block;