P Rimmer P Rimmer - 4 months ago 5
SQL Question

Having issue with my Select Case, output not working

SELECT date,
(CASE country WHEN 'France' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS france,
(CASE country WHEN 'Germany' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS germany,
(CASE country WHEN 'Italy' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS italy,
(CASE country WHEN 'Netherlands' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS netherlands,
(CASE country WHEN 'Nordic' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS nordic,
(CASE country WHEN 'Spain' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS spain,
(CASE country WHEN 'UK' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS uk
FROM report_adherence_daily
WHERE date>= '2016-07-04'
AND date<= '2016-07-10'
GROUP BY date,
country


Above is my code to transpose data into the format that I need but the result is showing as

Result of the query.

What I am missing is the extra bit that makes the results look like

Required Result.

I have tried to use max before the case and within the case and that hasn't worked. If I group by Date only, I'm missing most of my data which is the same as Grouping by Country. What am I missing?

Answer

Well you have already hardcoded the country names so this isn't so bad......

SELECT 
  Date, 
  Sum(France) as France, 
  Sum(Germany) as Germany,
  Sum(Italy) as Italy,
  Sum(Netherlands) as Netherlands,
  Sum(Nordic) as Nordic,
  Sum(Spain) as Spain,
  Sum(Uk) as UK
FROM
(
SELECT date,
       (CASE country WHEN 'France' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS france,
       (CASE country WHEN 'Germany' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS germany,
       (CASE country WHEN 'Italy' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS italy,
       (CASE country WHEN 'Netherlands' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS netherlands,
       (CASE country WHEN 'Nordic' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS nordic,
       (CASE country WHEN 'Spain' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS spain,
       (CASE country WHEN 'UK' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS uk
FROM report_adherence_daily
WHERE date>= '2016-07-04'
  AND date<= '2016-07-10'
GROUP BY date,
         country
)SUB_Q
GROUP BY Date
Comments