mysql query to add entries of multiple rows

Question

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

eggyal · Accepted Answer · 2012-12-18 10:20:35Z

2

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.

edited Dec 18, 2012 at 10:20

answered Dec 18, 2012 at 10:14

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

8 Comments

Manse Over a year ago

so group by uses the selected data rather than the table data ? (if that makes sense ?)

eggyal Over a year ago

@ManseUK: I take that back; I tested it and it didn't work as expected. Better to alias the column unambiguously.

Bhavik Shah Over a year ago

@eggyal : can you suggest me a book or website which could help me to learn these type of advanced queries and also which are better performance wise like you commented on bluefeet's post

eggyal Over a year ago

@BhavikShah: The best thing that you could do is read the MySQL manual - for the most part, it's very accessible and well-written and you can learn pretty much everything there is to know from it.

Bhavik Shah Over a year ago

@eggyal : also can I set a default value in case. like if the product is not in A,B,C,D,E then OTHER

|

Taryn · Accepted Answer · 2012-12-18 10:18:12Z

1

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

answered Dec 18, 2012 at 10:18

Taryn

249k57 gold badges374 silver badges409 bronze badges

1 Comment

eggyal Over a year ago

Not very index-friendly. Better to do the grouping first (using an index) then aggregate the groups after.

Collectives™ on Stack Overflow

mysql query to add entries of multiple rows

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related