CODEWITHSUNDEEP
mysqlsql
Kamrul Khan
Kamrul Khan

Reputation: 3360

MySQL Join two queries in same table

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?

Upvotes: 1

Views: 185

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

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;

Upvotes: 4

Related Questions