NeoS NeoS - 3 months ago 18
MySQL Question

self join with sum field

I have data like this
table t_prod

+---------+----------+-------------+-----------+----------+
|t_prod_id|t_prod_lot|t_prod_sublot|t_prod_card|t_prod_qty|
+---------+----------+-------------+-----------+----------+
| 4 | R001 | 1 | 1 | 6000 |
| 5 | R001 | 1 | 2 | 6000 |
| 6 | R001 | 1 | 3 | 6000 |
| 10 | R001 | 1 | 4 | 6000 |
| 11 | R001 | 1 | 5 | 6000 |
| 12 | R001 | 1 | 6 | 6000 |
| 13 | R001 | 2 | 1 | 6000 |
| 34 | R001 | 2 | 2 | 6000 |
| 36 | R001 | 2 | 3 | 2000 |
+---------+----------+-------------+-----------+----------+


and i want result like this when i select t_prod_lot = R001. count t_prod_sublot when same value and sum t_prod_qty when t_prod_sublot same value

+---------+----------+-------------+-----------+----------+--------------------+---------------+
|t_prod_id|t_prod_lot|t_prod_sublot|t_prod_card|t_prod_qty|count(t_prod_sublot)|sum(t_prod_qty)|
+---------+----------+-------------+-----------+----------+--------------------+---------------+
| 4 | R001 | 1 | 1 | 6000 | 6 | 36000 |
| 5 | R001 | 1 | 2 | 6000 | 6 | 36000 |
| 6 | R001 | 1 | 3 | 6000 | 6 | 36000 |
| 10 | R001 | 1 | 4 | 6000 | 6 | 36000 |
| 11 | R001 | 1 | 5 | 6000 | 6 | 36000 |
| 12 | R001 | 1 | 6 | 6000 | 6 | 36000 |
| 13 | R001 | 2 | 1 | 6000 | 3 | 14000 |
| 34 | R001 | 2 | 2 | 6000 | 3 | 14000 |
| 36 | R001 | 2 | 3 | 2000 | 3 | 14000 |
+---------+----------+-------------+-----------+----------+--------------------+---------------+


what is the query to produce this result ?
Sql Fiddle

Answer

Here's one option using a join with a subquery to count and sum the values:

select *
from t_prod t join (
  select t_prod_lot, t_prod_sublot, 
        count(t_prod_card) t_prod_card_cnt, 
        sum(t_prod_qty) t_prod_qty_sum
  from t_prod
  group by t_prod_lot, t_prod_sublot
) t2 on t.t_prod_lot = t2.t_prod_lot and t.t_prod_sublot = t2.t_prod_sublot
Comments