CODEWITHSUNDEEP
mysql
user3510431
user3510431

Reputation: 55

Finding SUM based on a Condition in MySQL

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

Answers (2)

Gordon Linoff
Gordon Linoff

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

Subin Chalil
Subin Chalil

Reputation: 3660

You can use UNION to achieve what you want.

Sample output :enter image description here

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

Related Questions