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.

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 received some help on another forum and 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";