Beppe <[email protected]> wrote:
> SELECT products.product_id,
> (SELECT SUM(quantity) FROM transactions WHERE transactions.product_id
> = products.product_id AND transactions.flow = 1) AS loaded,
> (SELECT SUM(quantity) FROM transactions WHERE transactions.product_id
> = products.product_id AND transactions.flow = 0) AS unoaded,
> SUM(loaded - unloaded) AS stock
> FROM products
> WHERE products.enable = 1
What are you adding up with the last SUM? Do you want to sum (loaded -
unloaded) over all products? Then what does product_id mean in the SELECT
clause?
See if this helps:
select products.product_id,
sum(quantity * flow) loaded,
sum(quantity * (1 - flow)) unloaded,
sum(quantity * (2*flow - 1)) stock
from products join transactions using (product_id)
where products.enable = 1
group by products.product_id;
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
