Reputation: 55
In my table in MYSQL have category 1-12 and under category question 001-300, a have create sum from category 6+7+8+9+10 example
category question correct
1 1 3
1 2 9
2 1 8
6 1 2
7 1 9
12 10 3
result
category question correct
1 1 3
1 2 9
2 1 8
6+7 1 11
12 10 3
How to do it?
Upvotes: 0
Views: 95
Reputation: 1271241
Just use group by
with a case
statement:
select (case when min(category) = max(category) then min(category)
else group_concat(category order by category separator '+')
end) as category,
question, sum(correct) as correct
from mytable
group by (case when category in (6, 7, 8, 9, 10) then -1 else category end),
question;
Actually, this can be simplified to:
select group_concat(category order by category separator '+') as category,
question, sum(correct) as correct
from mytable
group by (case when category in (6, 7, 8, 9, 10) then -1 else category end),
question;
Upvotes: 1
Reputation: 3660
You can use UNION
to achieve what you want.
Here is SQLFiddle Demo
SELECT *
FROM table_name WHERE `category`<6
UNION
SELECT (GROUP_CONCAT(`category` SEPARATOR '+')) AS category,
(GROUP_CONCAT(DISTINCT `question` SEPARATOR '+')) AS question,
(SUM(`correct`)) AS correct
FROM table_name WHERE category BETWEEN 6 AND 10
UNION
SELECT *
FROM table_name WHERE `category`>10
Hope this helps.
Upvotes: 1