On 8 March 2011 05:05, Beppe <[email protected]> wrote:
> Hi all,
> I have this query that work fine
>
> 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
> FROM products
> WHERE products.enable = 1
>
> but when I try to make this
>
> 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
>
> I get the following error message
>
> no such column: loaded
>
> Any suggestion about the right syntax?
select pid, loaded, unloaded, ( loaded - unloaded ) as stock from
(
select product_id as pid,
( select sum( quantity ) from transactions as t where
t.product_id = p.product_id and
flow=1 ) as loaded,
( select sum( quantity ) from transactions as t where
t.product_id = p.product_id and
flow=0 ) as unloaded
from product p where enable = 1
);
As an alternative, use a join and reduce number of subquries:
select pid, sum( loaded ), sum( unloaded ), sum( loaded - unloaded )
as stock from
(
select p.product_id as pid,
case t.flow when 1 then quantity else 0 end as loaded,
case t.flow when 0 then quantity else 0 end as unloaded
from
product as p join transactions as t
on
p.product_id = t.product_id
where
p.enable = 1
)
group by pid;
Test with real data to see which is most efficient...
> regards
> Beppe
>
Regards,
Simon
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
