Finding SUM based on a Condition in MySQL

Question

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 resultcategory question correct 1 1 3 1 2 9 2 1 8 6+7 1 11 12 10 3

How to do it?

Your question doesn't really make sense. Where does the second line for "12" come from? Are you trying to modify the data or just query it? — Gordon Linoff
– Gordon Linoff, Commented Nov 18, 2015 at 12:26

Subin Chalil · Accepted Answer · 2015-11-18 12:47:01Z

1

You can use UNION to achieve what you want.

Sample output :

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.

edited Nov 18, 2015 at 12:47

answered Nov 18, 2015 at 12:38

Subin Chalil

3,7082 gold badges31 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-11-19 03:23:25Z

1

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;

answered Nov 19, 2015 at 3:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Finding SUM based on a Condition in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related