CODEWITHSUNDEEP
mysql
Bhavik Shah
Bhavik Shah

Reputation: 5183

mysql query to add entries of multiple rows

I have a query in mysql which gives me this

product    count(*)
-------    ---------
A           5
B           2
C           3
D           4
E           1

the query is simple like

select product,count(*) from dashboard group by product;

Now the problem is I wanted to merge the count of some products into other product so for example the expected output is

product    count(*)
-------    ---------
A           7
C           3
D           5

so count(A)=count(A) + count(B)

count(c) = count(C)

count(D)= count(D) + count(E)

I was thinking something like this

select case
when product = 'A' OR product = 'B' then ----
when product = 'C' then ----
end

Upvotes: 1

Views: 78

Answers (2)

eggyal
eggyal

Reputation: 125835

Group your existing results a second time (this has the significant advantage that it will use an index, if possible, to perform the first aggregation and then a much simpler sum over the smaller resultset to perform the second):

SELECT   CASE
           WHEN p IN ('A','B') THEN 'A'
           WHEN p IN ('C')     THEN 'C'
           WHEN p IN ('D','E') THEN 'D'
         END AS product, SUM(c)
FROM     (
           SELECT   product AS p, COUNT(*) AS c
           FROM     dashboard
           GROUP BY p
         ) t
GROUP BY product

See it on sqlfiddle.

Upvotes: 2

Taryn
Taryn

Reputation: 247650

You can place the changes to the product in a subquery:

select product, count(*) Total
from
(
  select 
    case
        when product in ('A','B') then 'A'
        when product in ('C')     then 'C'
        when product in ('D','E') then 'D'
     end AS product
  from dashboard
) src
group by product;

See SQL Fiddle with Demo

Upvotes: 1

Related Questions