Order Products Based On Sales

[rayfinkel2]

I am trying to order products in our online store based on sales.  For instance, if one product sold 15 times last week, it would be shown above a product that sold 8 times last week.  This is a typical online store with a database consisting of a products table, orders table (containing customers info), and an orders_prods table (containing the products sold for each order).  I need to create a SQL query for a postgresql database that will ORDER the products by amount sold in the last week.  I am simplifying this query quite a bit, but if someone can help me fix this query, I am sure I can add the additional parameters to it.

 

$getProducts = "SELECT orders_prods.prod_id, SUM(orders_prods.quantity) AS quantitySold, products.thumbnail, products.main_image, products.prod_price, products.prod_id, products.prod_name FROM products LEFT JOIN orders_prods ON (orders_prods.prod_id = products.prod_id) GROUP BY orders_prods.prod_id ORDER BY quantitySold";

 

Please HELP!!!!

[.josh]

well, i don't know what your structure looks like, but at face value, does it work, except for listing in ascending order? Just throw a DESC on the end.

[rayfinkel2]

I always get this error:

 

column "products.yandy_thumbnail" must appear in the GROUP BY clause or be used in an aggregate function

 

 

And, if I change the Group By to (products.yandy_thumbnail), I get an error for the next variable I am trying to pull out of the products table.  I think the problem has to do with the fact that the orders_prods table doesn't always have a prod_id to match the products table because the orders_prods table only contains prod_id's for products that have sold.

[rayfinkel2]

Whenever I try this query:

 

$getProducts = "SELECT orders_prods.prod_id, SUM(orders_prods.quantity) AS quantitySold, products.thumbnail, products.main_image, products.prod_price, products.prod_id, products.prod_name FROM products LEFT JOIN orders_prods ON (orders_prods.prod_id = products.prod_id) GROUP BY orders_prods.prod_id ORDER BY quantitySold";

 

 

I get this error:

 

column "products.thumbnail" must appear in the GROUP BY clause or be used in an aggregate function

 

 

I think that it might be caused by the orders_prods table not containing every prod_id that the products table contains.  The orders_prods table only contains prod_id's for products that were sold. 

 

Here is a query that I got to work, but it errors out (with the error above) when I SELECT anything from the products table:

 

SELECT orders_prods.prod_id, SUM(orders_prods.quantity)

FROM products INNER JOIN orders_prods ON products.prod_id = orders_prods.prod_id

WHERE orders_prods.order_date > '1/25/2007 1:12:28'

GROUP BY (orders_prods.prod_id)

ORDER BY SUM(orders_prods.quantity) DESC

 

 

Any insite into this problem would be great.  Thank you

[rayfinkel2]

I almost have this query working now.  The only problem I am having is that it doesn't pull every product out of the products table because there is not always a prod_id in the orders_prods table to match the prod_id in the products table (especially sold since last week). A prod_id for a product will only appear in the orders_prods table if an item has been sold. Here is what my full query is. Please let me know if there is anything I can change that would fix this.

 

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

[rayfinkel2]

I know it's: "AND orders_prods.order_date > '$dateSevenDaysAgo' " thats limiting the whole selection to only the products it finds sold within the last week, but I need it to limit this query to order by sales within the last week. 

 

[rayfinkel2]

But, I need it to show all the products whether they have sold or not.

[rayfinkel2]

maybe a subquery to determine whether something has sold in the last week?  Help Please

[rayfinkel2]

Well, i think I finally figured it out. Here it is:

 

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