0

I am inheriting some data in an awful format, originally a spreadsheet and turned into an SQL table and I'm using SQL (LibreOffice Base as prototype and then probably MySQL) to query the data.

My source data looks like:

opt1 teach_opt1 opt2 teach_opt2 opt3 teach_opt3 opt4 teach_opt4
French Good History Outstanding IT Requires improvement Maths Good
History Good French Outstanding Maths Good German Good

Where an option could be in any of opt{1,2,3,4} and a judgement about the option could be in any of teach_opt{1,2,3,4}.

This query helps a lot, by simply grouping all opt{1,2,3,4} with the same name and summing totals of each judgement:

SELECT "subject", "judgement", SUM("teaching") AS total FROM (
(SELECT "opt1" AS "subject", "teach_opt1" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt1" NOT LIKE 'None' AND "opt1" NOT LIKE '' AND "teach_opt1" NOT LIKE 'Don%do it' GROUP BY "opt1", "teach_opt1")
UNION ALL
(SELECT "opt2" AS "subject", "teach_opt2" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt2" NOT LIKE 'None' AND "opt2" NOT LIKE '' AND "teach_opt2" NOT LIKE 'Don%do it' GROUP BY "opt2", "teach_opt2")
UNION ALL
(SELECT "opt3" AS "subject", "teach_opt3" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt3" NOT LIKE 'None' AND "opt3" NOT LIKE '' AND "teach_opt3" NOT LIKE 'Don%do it' GROUP BY "opt3", "teach_opt3")
UNION ALL
(SELECT "opt4" AS "subject", "teach_opt4" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt4" NOT LIKE 'None' AND "opt4" NOT LIKE '' AND "teach_opt4" NOT LIKE 'Don%do it' GROUP BY "opt4", "teach_opt4")
) t
GROUP BY "subject", "judgement"
ORDER BY "subject", "judgement";

yields:

subject judgement TOTAL
French Good 15
French Outstanding 7
IT Good 11
IT Outstanding 14
IT Requires improvement 3

What I'd ultimately like to achieve is one row per subject, with named columns and totals. Unfortunately, not every subject has every judgement. For example, potentially "Geography", "Requires improvement" might not be anywhere in the table.

subject Outstanding Good Requires improvement
French 7 15 0
IT 14 11 3

I think from reading that PIVOT might be a useful function but I cannot work out where to go to achieve this?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
tornadof3
  • 234
  • 1
  • 8

1 Answers1

1

You can use conditional aggregation to pivot your intermediate results as follows.

Wrapping your current query (without its ordering criteria):

select subject,
    max(case when judgement='Outstanding' then total else 0 end) as Outstanding,
    max(case when judgement='Good' then total else 0 end) as Good,
    max(case when judgement='Requires improvement' then total else 0 end) as RequiresImprovement
from (

    select subject, judgement, Sum(teaching) as total from (
    (select opt1 as subject, teach_opt1 as judgement, Count(*) as teaching from raw where opt1 not like 'None' and opt1 not like '' and teach_opt1 not like 'Don%do it' group by opt1, teach_opt1)
    union all
    (select opt2 as subject, teach_opt2 as judgement, Count(*) as teaching from raw where opt2 not like 'None' and opt2 not like '' and teach_opt2 not like 'Don%do it' group by opt2, teach_opt2)
    union all
    (select opt3 as subject, teach_opt3 as judgement, Count(*) as teaching from raw where opt3 not like 'None' and opt3 not like '' and teach_opt3 not like 'Don%do it' group by opt3, teach_opt3)
    union all
    (select opt4 as subject, teach_opt4 as judgement, Count(*) as teaching from raw where opt4 not like 'None' and opt4 not like '' and teach_opt4 not like 'Don%do it' group by opt4, teach_opt4)
    ) t
    group by subject, judgement

)s
group by subject
order by subject
Stu
  • 30,392
  • 6
  • 14
  • 33