i used mysql version 5.7, i have a table production for every product, amount, and using many expeditions like this
+---------+-----------------+------+--------+---------+
| Product | Type_Expedition | Pack | Amount | Weight |
+---------+-----------------+------+--------+---------+
| Chicken | A | 1 | 2 | 2 |
| Beef | A | 1 | 2 | 2 |
| Lamb | B | 1 | 2 | 2 |
| Beef | B | 2 | 2 | 4 |
| Chicken | A | 3 | 2 | 6 |
| Lamb | A | 1 | 1 | 1 |
| Lamb | A | 1 | 1 | 1 |
+---------+-----------------+------+--------+---------+
how to calculate sum of weight and amount for type_expedition B and non-B (all type expedition except B) ?
i assume using this syntax (sorry i want to using dbfiddle.uk but it's error)
select product, type_expedition, pack, amount, weight, (sum(amount) where type_expedition = B), (sum(weight) where type_expedition = B) from my_table
expected results
+---------------------------------------------------+---+----+
| Total amount and weight for type_expedition B | 4 | 6 |
+---------------------------------------------------+---+----+
| Total amount and weight for type_expedition NON B | 8 | 12 |
+---------------------------------------------------+---+----+
You can use UNION ALL for the last 2 rows:
select t.Product, t.Type_Expedition, t.Pack, t.Amount, t.Weight
from (
select *, 0 sort from my_table
union all
select 'Total Amount and Weight for expedition B', null, null,
sum(amount),
sum(weight), 1
from my_table
where Type_Expedition = 'B'
union all
select 'Total Amount and Weight for expedition not B', null, null,
sum(amount),
sum(weight), 2
from my_table
where Type_Expedition <> 'B'
) t
order by t.sort
See the demo.
Results:
| Product | Type_Expedition | Pack | Amount | Weight |
| -------------------------------------------- | --------------- | ---- | ------ | ------ |
| Beef | A | 1 | 2 | 2 |
| Chicken | A | 3 | 2 | 6 |
| Lamb | B | 1 | 2 | 2 |
| Lamb | A | 1 | 1 | 1 |
| Chicken | A | 1 | 2 | 2 |
| Beef | B | 2 | 2 | 4 |
| Lamb | A | 1 | 1 | 1 |
| Total Amount and Weight for expedition B | | | 4 | 6 |
| Total Amount and Weight for expedition not B | | | 8 | 12 |
If you want only the last 2 rows with the totals:
select
case Type_Expedition
when 'B' then 'Total Amount and Weight for expedition B'
else 'Total Amount and Weight for expedition not B'
end type,
sum(amount),
sum(weight)
from my_table
group by type
See the demo.
Results:
| Total Amount and Weight for expedition B | 4 | 6 |
| Total Amount and Weight for expedition not B | 8 | 12 |