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

Anything you add to your select statement, must be added to your group by statement. At least, anything you're not performing an aggregate function (like SUM) on.

SELECT orders_prods.prod_id, SUM(orders_prods.quantity),
products.product
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), products.product
ORDER BY SUM(orders_prods.quantity) DESC

Thanks so much Red Neck! The query is definately getting almost perfect 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. 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?

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

Another possibility is to add the "order_date > '$dateSevenDaysAgo'" condition to the LEFT OUTER JOIN's ON condition. But indeed, with a subquery the query might becone more performant.
Optimally, you should even move the GROUP BY into the subquery.
It will speed up the query, AND make the extra columns in your GROUP BY superfluous.

Thanks Peter,

I really appreciate the feedback and any information about how to increase the speed of this query. I tried changing the group by information into the subquery in a number of different ways and could not get it to work. If you could rewrite this query to show me how you think it would work best, I would really appreciate it. Right now the query is running really fast, but I am always trying to increase speed.

Thanks again,
Kyle

Let's give it a try ...

Thanks again Peter,

I used your query and it pulls exactly the same listings as my query. I didn't really see a speed increase, but I realize that by limiting the number of products being grouped, it should increase the speed, so I am going to use your query. The query that I ended up with is this:

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


The only thing I don't understand is when I use DESC at the end of the query, it puts all the products that have been sold recently at the very end instead of the beginning. So, I have to use ASC which pulls all the recently sold products to the beginning. I still run into one problem though. The products that it shows in the beginning are the products that have recently sold. However, it orders them in reverse order. A product that sold 5 times comes after a product that sold 1 time, but at least they are at the top of the page. Do you have any idea why this might be happening? I tried ordering the subquery as well and that didn't seem to do anything.

Thanks so much,
Kyle

Can anyone tell me why the query above is putting the products that have been sold recently on the top (which is what I want), but ordering those products that have been sold recently in reverse? I am still stumped. The products that have not been sold within the last week are showing up below the products that have sold within the last week, which is great.

Thank you,
Kyle

You just need to sort on two fields, not one.
Try adding the prod_id to your ORDER BY statement after qtysold.

Thanks for the advise again RedNeck. I really appreciate how you have spent so much time replying to my posts. I tried about 20 different combinations of "Order By's" and could not get it to order it the correct way.

This is an example of one I tried that brought the recently sold products to the very end of the list:

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

I can't seem to find the right combination. Any suggestions?

Thanks again,
Kyle

Excuse me for asking a stupid question, but...

Your sales numbers wouldn't happen to be stored as negative number, would they? That would cause them to fall lower in the list when sorted in descending order.

This is the result I would expect you to get:

All items that "sold recently" at the top of your list, sorted in descending order by qty sold; and then all the products that
didn't sell sorted (0 qty sold) alphabetically below that.

Like this:

prod_id-----qtysold
ghi-----------15
abc----------10
def-----------7
lmo-----------0
pqr-----------0
xyz-----------0