Aggregate and calculate values in mysql
Aggregate and calculate values in mysql
I have a table with some incoming and outgoing data.
+====+===========+==========+============+
| id | flow(int) | quantity | product_id |
+====+===========+==========+============+
| 1 | 0 | 100 | 1 |
+----+-----------+----------+------------+
| 2 | 1 | 20 | 1 |
+----+-----------+----------+------------+
| 3 | 1 | 30 | 1 |
+----+-----------+----------+------------+
| 4 | 0 | 10 | 1 |
+----+-----------+----------+------------+
| 5 | 1 | 30 | 2 |
+----+-----------+----------+------------+
| 6 | 2 | 10 | 2 |
+----+-----------+----------+------------+
| 7 | 0 | 10 | 2 |
+====+===========+==========+============+
Column flow is a type of enum, 0 - incoming, 1 and 2 - outgoing operation.
How can I get current "balance" of specific product?
Product 1 should have "balance" 60 = incoming(100) - outgoing(20) - outgoing(30) + incoming(10).
And Product 2 should have -30 "balance" = outgoing(30) - outgoing(10) - incoming(10).
Is is possible to make in any efficient way with single query?
@Molfar please consider accepting the answer which works best for you :)
– Madhur Bhaiya
Sep 11 '18 at 16:57
3 Answers
3
Use the following:
SELECT product_id,
SUM( IF(flow IN ('1','2'),
-1*quantity,
quantity) ) AS balance
FROM table_name
GROUP BY product_id;
SELECT product_id, SUM( IF(flow=0, quantity,(-1*quantity)) ) AS balance FROM table_name GROUP BY product_id;
– Mohammed Jubayer
Sep 11 '18 at 18:29
If (false) or if (!true) are same things only. So if condition can be varied keeping logic same
– Madhur Bhaiya
Sep 11 '18 at 18:32
This would be conditional aggregation:
select product_id,
sum(case when flow like 'out_%' then - quantity else quantity end) as net_quantity
from t
group by product_id;
With incoming_product as(
select product_id,sum(quantity) res
from my_table
where flow = 0
group by product_id
),
outgoing_product as(
select product_id,sum(quantity) res
from my_table
where flow <> 0
group by product_id
)
select t1.product_id, t1.res - t2.res
from incoming_product t1,outgoing_product t2
where t1.product_id = t2.product_id;
is it more efficient than sum..if?
– Molfar
Sep 11 '18 at 15:16
no but it works
– Alays
Sep 11 '18 at 15:20
It will only work for MySQL version >= 8.0
– Madhur Bhaiya
Sep 12 '18 at 5:22
He didn't specifie his sgbd.
– Alays
Sep 12 '18 at 7:14
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Add some more sample table data (e.g. another product). Also specify the expected result set.
– jarlh
Sep 11 '18 at 14:50