In article <[EMAIL PROTECTED]>,
Ville Mattila <[EMAIL PROTECTED]> writes:
> When I try to find out the current amount of products in our stock,
> and ordered quantities I use this query:
> SELECT p.id, SUM(out.quantity) ordered_out, SUM(in.quantity)
> ordered_in FROM products p LEFT JOIN outorders out ON (out.productid =
> p.id) LEFT JOIN inorders (in.productid = p.id);
> Even I have only one "OutOrder" for certain product, I get ordered_out
> value as 2. ordered_in value is anyway correct. If I remove all things
> related to "InOrders" I will get correct result to ordered_out.
> Have I missed something? Isn't LEFT JOIN the correct query to join
> tables to the "main" table (in this case products)?
Drop the sum() aggregate and you'll see what went wrong: you get the
same "out" row for every matching "in" row and vice versa; thus your
sums are in general multiples of the correct values. To remedy that,
you need a division:
SELECT p.id,
SUM(out.quantity) / COUNT(DISTINCT in.id) ordered_out,
SUM(in.quantity) / COUNT(DISTINCT out.id) ordered_in
FROM products p
LEFT JOIN outorders out ON out.productid = p.id
LEFT JOIN inorders ON in.productid = p.id
GROUP BY p.id;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
