Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Igor Tandetnik]

Gert Cuykens 
wrote:
> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
> from PRODUCTS t left join ORDERS o on t.pid = o.pid group by t.pid
>
> Works also thanks.
> So you do not believe the following has a performance penalty ?
>
> SELECT pid, txt, price, qty-coalesce((SELECT sum(qty) FROM orders
> WHERE orders.pid = products.pid),0) FROM products

I don't see why it should, but when in doubt, test and measure.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Gert Cuykens]

On Wed, Sep 16, 2009 at 9:50 PM, Igor Tandetnik  wrote:
> Gert Cuykens 
> wrote:
>> On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik
>>  wrote:
>>> Gert Cuykens 
>>> wrote:
 On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik
  wrote:
>>>
> Perhaps your query could be a bit clearer when written this way:
>
> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0)
> onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid

 This does not show me the new products that are not ordered yet
>>>
>>> Are you sure? I don't see why it wouldn't.
>>
>> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
>> from PRODUCTS t left join ORDERS o on t.pid = o.pid
>>
>> shows 1 product because I only have 1 order with that product
>
> This query is different from the one I posted. Mine had "where t.pid =
> ?;", yours doesn't. If you want a report for all products, add the
> following clause:
>
> group by t.pid;
>
>> SELECT t.pid,
>>         t.txt,
>>         t.price,
>>         t.qty - IFNULL(qs.qty_sold, 0) "onhand_qty"
>>    FROM PRODUCTS t
>> LEFT JOIN (SELECT o.pid,
>>                 SUM(o.qty) "qty_sold"
>>            FROM ORDERS o) qs ON qs."o.pid" = t.pid
>>
>> shows all products
>
> Compare this statement with the one you mentioned in your original post.
> Lacking mind-reading abilities, I was trying to help you with the
> statement you actually asked about, not the one you were thinking about.

select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
from PRODUCTS t left join ORDERS o on t.pid = o.pid group by t.pid

Works also thanks.
So you do not believe the following has a performance penalty ?

SELECT pid, txt, price, qty-coalesce((SELECT sum(qty) FROM orders
WHERE orders.pid = products.pid),0) FROM products
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Igor Tandetnik]

Gert Cuykens 
wrote:
> On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik
>  wrote:
>> Gert Cuykens 
>> wrote:
>>> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik
>>>  wrote:
>>
 Perhaps your query could be a bit clearer when written this way:

 select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0)
 onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid
>>>
>>> This does not show me the new products that are not ordered yet
>>
>> Are you sure? I don't see why it wouldn't.
>
> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
> from PRODUCTS t left join ORDERS o on t.pid = o.pid
>
> shows 1 product because I only have 1 order with that product

This query is different from the one I posted. Mine had "where t.pid = 
?;", yours doesn't. If you want a report for all products, add the 
following clause:

group by t.pid;

> SELECT t.pid,
> t.txt,
> t.price,
> t.qty - IFNULL(qs.qty_sold, 0) "onhand_qty"
>FROM PRODUCTS t
> LEFT JOIN (SELECT o.pid,
> SUM(o.qty) "qty_sold"
>FROM ORDERS o) qs ON qs."o.pid" = t.pid
>
> shows all products

Compare this statement with the one you mentioned in your original post. 
Lacking mind-reading abilities, I was trying to help you with the 
statement you actually asked about, not the one you were thinking about.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Gert Cuykens]

On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik  wrote:
> Gert Cuykens 
> wrote:
>> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik 
>> wrote:
>
>>> Perhaps your query could be a bit clearer when written this way:
>>>
>>> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0)
>>> onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid
>>
>> This does not show me the new products that are not ordered yet
>
> Are you sure? I don't see why it wouldn't.

select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
from PRODUCTS t left join ORDERS o on t.pid = o.pid

shows 1 product because I only have 1 order with that product

SELECT t.pid,
 t.txt,
 t.price,
 t.qty - IFNULL(qs.qty_sold, 0) "onhand_qty"
FROM PRODUCTS t
LEFT JOIN (SELECT o.pid,
 SUM(o.qty) "qty_sold"
FROM ORDERS o) qs ON qs."o.pid" = t.pid

shows all products

>>> or this way
>>>
>>> select t.pid, t.txt, t.price, t.qty - IFNULL(
>>> (select sum(o.qty) from ORDERS o where t.pid = o.pid), 0) onhand_qty
>>> from PRODUCTS t where t.pid = ?;
>>>
>>
>> I learned that this would be a performance issue doing it like that.
>>
>> http://stackoverflow.com/questions/1417889/sqlite3-get-product-onhand-quantity
>
> You "learned"? Do you mean, you measured and discovered this to be the
> case? The answer in that thread you are basing this claim on is largely
> nonsense, in my humble opinion. For one thing, your query only returns
> one row, so running a subselect "for every row returned" means running
> it once. For another, how does the poster believe joins are calculated -
> black magic?
>

The where t.pid=? should have been removed from my original question,
so it show the complete list of products.
I did not measured it, it sounded logic.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Igor Tandetnik]

Gert Cuykens 
wrote:
> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik 
> wrote:

>> Perhaps your query could be a bit clearer when written this way:
>>
>> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0)
>> onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid
>
> This does not show me the new products that are not ordered yet

Are you sure? I don't see why it wouldn't.

>> or this way
>>
>> select t.pid, t.txt, t.price, t.qty - IFNULL(
>> (select sum(o.qty) from ORDERS o where t.pid = o.pid), 0) onhand_qty
>> from PRODUCTS t where t.pid = ?;
>>
>
> I learned that this would be a performance issue doing it like that.
>
> http://stackoverflow.com/questions/1417889/sqlite3-get-product-onhand-quantity

You "learned"? Do you mean, you measured and discovered this to be the 
case? The answer in that thread you are basing this claim on is largely 
nonsense, in my humble opinion. For one thing, your query only returns 
one row, so running a subselect "for every row returned" means running 
it once. For another, how does the poster believe joins are calculated - 
black magic?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Gert Cuykens]

On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik  wrote:
> Gert Cuykens 
> wrote:
>>   SELECT t.pid,
>>          t.txt,
>>          t.price,
>>          t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty'
>>     FROM PRODUCTS t
>> LEFT JOIN (SELECT o.pid,
>>                  SUM(o.qty) 'qty_sold'
>>             FROM ORDERS o) qs ON qs.pid = t.pid
>>    WHERE t.pid = ?
>>
>> i have trouble running this statement on sqlite3
>> It tels me qs.pid does not exist
>
> And indeed it doesn't. But there is a column named qs."o.pid". You may
> want to assign an alias to this column, just as you did with qs.qty_sold
>
>> when i rename it to pid it subtracts
>> the wrong values
>
> Because now it refers to t.pid, and the condition (t.pid = t.pid) is
> always true.

qs."o.pid" = t.pid works thanks

> Perhaps your query could be a bit clearer when written this way:
>
> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
> from PRODUCTS t left join ORDERS o on t.pid = o.pid

This does not show me the new products that are not ordered yet

> or this way
>
> select t.pid, t.txt, t.price, t.qty - IFNULL(
>    (select sum(o.qty) from ORDERS o where t.pid = o.pid), 0) onhand_qty
> from PRODUCTS t where t.pid = ?;
>

I learned that this would be a performance issue doing it like that.

http://stackoverflow.com/questions/1417889/sqlite3-get-product-onhand-quantity
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Igor Tandetnik]

Gert Cuykens 
wrote:
>   SELECT t.pid,
>  t.txt,
>  t.price,
>  t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty'
> FROM PRODUCTS t
> LEFT JOIN (SELECT o.pid,
>  SUM(o.qty) 'qty_sold'
> FROM ORDERS o) qs ON qs.pid = t.pid
>WHERE t.pid = ?
>
> i have trouble running this statement on sqlite3
> It tels me qs.pid does not exist

And indeed it doesn't. But there is a column named qs."o.pid". You may 
want to assign an alias to this column, just as you did with qs.qty_sold

> when i rename it to pid it subtracts
> the wrong values

Because now it refers to t.pid, and the condition (t.pid = t.pid) is 
always true.

Perhaps your query could be a bit clearer when written this way:

select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty
from PRODUCTS t left join ORDERS o on t.pid = o.pid
where t.pid = ?;

or this way

select t.pid, t.txt, t.price, t.qty - IFNULL(
(select sum(o.qty) from ORDERS o where t.pid = o.pid), 0) onhand_qty
from PRODUCTS t where t.pid = ?;

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Simon Davies]

009/9/15 Gert Cuykens :
>   SELECT t.pid,
>          t.txt,
>          t.price,
>          t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty'
>     FROM PRODUCTS t
> LEFT JOIN (SELECT o.pid,
>                  SUM(o.qty) 'qty_sold'
>             FROM ORDERS o) qs ON qs.pid = t.pid
>    WHERE t.pid = ?
>
> i have trouble running this statement on sqlite3
> It tels me qs.pid does not exist, when i rename it to pid it subtracts
> the wrong values when ORDERS contains one record and PRODUCTS more the
> one?

It's late, so I am not putting in the time to generate test data in
your schema, but you should probably ensure that your result column in
your result table is aliased properly:

  SELECT t.pid,
 t.txt,
 t.price,
 t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty'
FROM PRODUCTS t
LEFT JOIN (SELECT o.pid as pid, -- alias this column
 SUM(o.qty) 'qty_sold'
FROM ORDERS o) qs ON qs.pid = t.pid
   WHERE t.pid = ?;

it certainly resolves error messages about "qs.pid does not exist".

>
>
> CREATE TABLE PRODUCTS (
>    pid     INTEGER PRIMARY KEY,
>    txt     VARCHAR(64),
>    price   BIGINT UNSIGNED,
>    qty     BIGINT
> );
>
> CREATE TABLE ORDERS (
>    oid     INTEGER,
>    pid     BIGINT UNSIGNED,
>    qty     BIGINT UNSIGNED,
>    time   DATETIME,
>    PRIMARY KEY(oid,pid)
> );

You should also be aware that single quotes are delimiters for
literals, not for identifiers. For identifiers use double quotes.
(SQLite is tolerant of the usage in the case above).

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users