Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Order Products Based On Sales

Status
Not open for further replies.

rayfinkel2

Programmer
Feb 2, 2007
8
US
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
 
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
 
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.
 
But, I need it to show all the products whether they have sold or not.
 
maybe a subquery to determine whether something has sold in the last week? Help Please
 
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";
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top