ORDER BY problem with JOINs

[rayfinkel2]

Here is my problem and it appears that it may only be a problem with PostgreSQL:

I am trying to order the products on my online store showing the products that have been sold the most amount of times within the last week on the top.

There are a lot of products that may not have been sold within the last week, so may not appear in the orders_prods table.

This is how I want them to be ordered:

prod_id-----qtysold
ghi-----------15
abc----------10
def-----------7
lmo-----------0
pqr-----------0
xyz-----------0



Here is my current query:

SELECT op.quantitySold, p.thumbnail, p.main_image, p.prod_price, p.prod_id, p.prod_name FROM products AS p INNER JOIN product_categories ON p.prod_id =

product_categories.prod_id LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY

prod_id) AS op ON op.prod_id = p.prod_id WHERE product_categories.cat_id = '$catID' ORDER BY op.quantitySold ASC

This Query orders the products like this:

prod_id-----qtysold
def-----------7
abc----------10
ghi-----------15
lmo-----------0
pqr-----------0
xyz-----------0


When I order quantitySold by DESC, the products that have been sold recently always appear on the bottom no matter what I try.

Here is an example query:

SELECT op.quantitySold, p.thumbnail, p.main_image, p.prod_price, p.prod_id, p.prod_name FROM products AS p INNER JOIN product_categories ON p.prod_id =

product_categories.prod_id LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY

prod_id) AS op ON op.prod_id = p.prod_id WHERE product_categories.cat_id = '$catID' ORDER BY op.quantitySold DESC, p.prod_id ASC

And I get this result:

prod_id-----qtysold
lmo-----------0
pqr-----------0
xyz-----------0
ghi-----------15
abc----------10
def-----------7


If anyone has any idea why this is happening, I would greatly appreciate the help.

Thank You,
Kyle

 

[ceco]

actually at first glance I don't see any reason, but why do you do that

SELECT op.quantitySold, ..... LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY

prod_id) AS op ON op.prod_id = p.prod_id

instead of

SELECT (SELECT SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' WHERE prod_id = p.prod_id) AS quantitySold ....



(as an idea try ORDER BY quantitySold - without the op.)

 

[rayfinkel2]

Thanks for the help ceco,

I am sorry, but I posted to so many forums that I forgot to put my final answer on this forum. The conclusion that I came to (with a lot of help from a couple of forums) is this final query. And it works great and seems to execute very quickly. What I forgot is that postgreSQL treats null values differently than zero, so I needed to use the COALESCE function.

SELECT op.quantitySold, p.thumbnail, p.main_image, p.prod_price, p.prod_id, p.prod_name FROM products AS p
INNER JOIN product_categories ON p.prod_id = product_categories.prod_id
LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY prod_id) AS op ON op.prod_id = p.prod_id
WHERE product_categories.cat_id = '$catID'
ORDER BY COALESCE(op.quantitySold, 0) ASC


Take care,
Kyle