×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Order Products Based On Sales

Order Products Based On Sales

Order Products Based On Sales

(OP)
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

RE: Order Products Based On Sales

(OP)
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

RE: Order Products Based On Sales

(OP)
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.

RE: Order Products Based On Sales

(OP)
But, I need it to show all the products whether they have sold or not.

RE: Order Products Based On Sales

(OP)
maybe a subquery to determine whether something has sold in the last week?   Help Please

RE: Order Products Based On Sales

(OP)
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";

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close