CODEWITHSUNDEEP
mysqlsqljoin
umesh brainser
umesh brainser

Reputation: 23

Total purchase, sales and stock remained for each product using mysql

I am trying to get total purchase, sales and sotck remained for reach product using mysql query which is as follows:

select fk_product_id,
(select sum(quantity) from entry_products where status =0 ) as total_purchase,
(select sum(quantity) from entry_products where status =1)as total_sales,
(select sum(quantity) from entry_products where status =0 ) -
(select sum(quantity) from entry_products where status =1) as stock

from entry_products group by fk_product_id

Output

fk_product_id     total_purchase    total_sales     stock
1                   1700                 660         1040
2                   1700                 660         1040
3                   1700                 660         1040

My Expected Output is
fk_product_id     total_purchase    total_sales     stock
1                   350                  200         150
2                   1100                 460         640
3                   250                  0           250

Upvotes: 1

Views: 314

Answers (1)

forpas
forpas

Reputation: 164064

You need conditional aggregation:

select fk_product_id,
       sum(case when status = 0 then quantity else 0 end) as total_purchase,
       sum(case when status = 1 then quantity else 0 end) as total_sales,
       sum(case when status = 0 then quantity else 0 end) - sum(case when status = 1 then quantity else 0 end) as stock
from entry_products 
group by fk_product_id

Because MySql evaluates boolean expressions as 1 for true or 0 for false, the code could be written also like this:

select fk_product_id,
       sum((status = 0) * quantity) as total_purchase,
       sum((status = 1) * quantity) as total_sales,
       sum((status = 0) * quantity) - sum((status = 1) * quantity) as stock
from entry_products 
group by fk_product_id

Upvotes: 1

Related Questions