Jump to content

Order Products Based On Sales


rayfinkel2

Recommended Posts

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!!!!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

:)

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";

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.