CODEWITHSUNDEEP
mysqlsum
Luis Garcia
Luis Garcia

Reputation: 89

MySQL SUM IF column has specific value

Is there a way to sum rows only when they meet certain condition?, If they dont they must be copied to the new table. For example, if i have this table

| id | A | B |
--------------
|  1 | a | 2 |
|  1 | b | 4 |
|  1 | c | 1 |
|  2 | a | 4 |
|  3 | a | 1 |
|  3 | b | 5 |

I want an output like this

| id |  A  | B |
--------------
|  1 | a,b | 6 |
|  1 |  c  | 1 |
|  2 |  a  | 4 |
|  3 | a,b | 6 |

It will only sum if column 'A' is 'a' or 'b', it will just copy the value if its 'c'

Upvotes: 4

Views: 883

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • You can do this in two different Select queries. In first Select query, just consider the cases where A has either values 'a' or 'b'. In the second Select query, consider the rest of the values (A NOT IN ('a','b'))
  • Use UNION ALL to combine the results into a Derived Table.
  • Order the Derived table result-set in ascending order by id.
  • We use aggregation functions like Group_concat() and Sum() to get comma separated string (for a and b), and sum of the B values, respectively.

Try the following:

SELECT dt.* 
FROM 
(
  SELECT id, 
         GROUP_CONCAT(DISTINCT A) AS A,
         SUM(B) AS B
  FROM your_table 
  WHERE A IN ('a','b') 
  GROUP BY id

  UNION ALL 

  SELECT id, 
         A,
         B
  FROM your_table 
  WHERE A NOT IN ('a', 'b')
  GROUP BY id, A, B
) AS dt 

ORDER BY dt.id ASC

Upvotes: 2

Alex
Alex

Reputation: 17289

Your example does not show all possible cases.

But I think that probably you don't need to overcomplicate solution with UNION

http://sqlfiddle.com/#!9/d473d3/6

SELECT id, 
   GROUP_CONCAT(A), 
   SUM(B)
FROM abc
GROUP BY CONCAT(id, IF(A IN ('a','b'),'$',A))

Upvotes: 0

Related Questions