|school_name| class| medium| total 
 ---------- ------  ------ ------
srk           1      english  13
srk           2      english  14
srk           3      english  15
srk           1      french   16
srk           2       french   16
srk           3       french   18
vrk           1       english   17
vrk           1       french    18

I want that output by

schoo_name |class1eng |class1french| class2eng| class2french| class3english| class3french [output needed][ otput required

output

CodePudding user response：

Seems to be a simple ask, assumed you also want to order your results. Please check below query if that helps

SELECT school_name, class, medium, SUM(total) AS Total
FROM <Table Name>
GROUP BY school_name, class, medium

CodePudding user response：

You’re looking for multiple select statements along with appropriate cases to satisfy. This should work for you

Select
school_name,
Sum(Case when (class=1 and medium=‘English’) then total else 0 end) as class1english,

Sum(Case when (class=1 and medium=‘French’) then total else 0 end) as class1french,

Sum(Case when (class=2 and medium=‘English’) then total else 0 end) as class2english,

Sum(Case when (class=2 and medium=‘French’) then total else 0 end) as class2french,


Sum(Case when (class=3 and medium=‘English’) then total else 0 end) as class3english,

Sum(Case when (class=3 and medium=‘French’) then total else 0 end) as class3french

From table_name
Group by
school_name