rayfinkel2
Programmer
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
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