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