MySQL Join two queries in same table

Question

mysql> select * from fact_lab;
    +---------+--------+-----+
    | product | amount | box |
    +---------+--------+-----+
    | a       |    100 |   1 |
    | b       |    200 |   1 |
    | c       |     50 |   1 |
    | a       |    200 |   2 |
    | b       |    100 |   2 |
    | c       |     50 |   2 |
    | a       |    100 |   3 |
    | b       |    200 |   3 |
    | c       |     50 |   3 |
    +---------+--------+-----+
    9 rows in set (0.00 sec)

I am looking for an output where I can see the total sum of amounts for each product that will show a comparison with amounts for box 2. So, the output should be like the below

+---------+--------+-----+
| product | amount | inbox2 |
+---------+--------+-----+
| a       |    400 | 200 |
| b       |    500 | 100 |
| c       |    150 |  50 |
+---------+--------+-----+

How can i get this result in a single query?

Gordon Linoff · Accepted Answer · 2014-05-22 11:49:06Z

4

You can get what you want with aggregation. The group by is a basic part of the SQL language. If you don't understand it, then you should study up a bit more on the language.

The second part uses condition aggregation. That is, a case statement is the argument to sum():

select fl.product, sum(amount) as amount,
       sum(case when box = 2 then amount else 0 end) as inbox2
from fact_lab fl
group by fl.product;

answered May 22, 2014 at 11:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

MySQL Join two queries in same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related