I'm trying to learn sql query and i want to solve the following problem.
Assume the table as follow:
| id | schoolname | totalA | totalB | grade |
|---|---|---|---|---|
| 1 | school A | 5 | 5 | 1 |
| 2 | school A | 5 | 5 | 2 |
| 3 | school B | 5 | 5 | 1 |
| 4 | school B | 5 | 5 | 2 |
Select schoolname
SUM(CASE WHEN (grade='1' ) THEN totalA ELSE 0 END) AS t1A,
SUM(CASE WHEN (grade='1' ) THEN totalB ELSE 0 END) AS t1B,
SUM(CASE WHEN (grade='2' ) THEN totalA ELSE 0 END) AS t2A,
SUM(CASE WHEN (grade='2' ) THEN totalB ELSE 0 END) AS t2B,
I would like to know if it is possible to add both totalA and B in one case condition.
I tried
Select schoolname
CASE WHEN (grade='1' ) THEN SUM(totalA totalB) ELSE 0 END AS Grade 1,
CASE WHEN (grade='2' ) THEN SUM(totalA totalB) ELSE 0 END AS Grade 2,
From school
Group By schoolname
But it give me error.
I want to achieve the following:
| schoolname | grade 1 | grade 2 |
|---|---|---|
| School A | 10 | 10 |
| School B | 10 | 10 |
CodePudding user response：
Yes it is possible by group by query of MySQL in the same table.
For that you need to use this type of query:
SELECT schoolname,
SUM(CASE WHEN (grade = 1) THEN totalA totalB ELSE 0 END) AS grade_1,
SUM(CASE WHEN (grade = 2) THEN totalA totalB ELSE 0 END) AS grade_2
FROM school
GROUP BY schoolname