Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Feb 2007
    Posts
    25

    Exclamation Unanswered: Order Products Based On Sales

    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

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    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
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2007
    Posts
    25

    Exclamation Order Products By Sales

    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

  4. #4
    Join Date
    Feb 2007
    Posts
    25
    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.

  5. #5
    Join Date
    Feb 2007
    Posts
    25
    But, I need it to show all the products whether they have sold or not.

  6. #6
    Join Date
    Feb 2007
    Posts
    25
    Maybe a subquery?

  7. #7
    Join Date
    Feb 2007
    Posts
    25

    Smile

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

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Feb 2007
    Posts
    25

    Talking

    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

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by rayfinkel2
    If you could rewrite this query to show me how you think it would work best
    Let's give it a try ...
    Code:
    SELECT p.prod_id, op.quantitySold,
           p.thumbnail, p.main_image, p.prod_price, p.prod_name
    FROM   products AS p
           LEFT OUTER JOIN
           (SELECT prod_id, SUM(quantity) AS quantitySold
            FROM orders_prods
            GROUP BY prod_id) AS op
           ON op.prod_id = p.prod_id
    ORDER BY quantitySold DESC
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Feb 2007
    Posts
    25

    Talking

    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

  12. #12
    Join Date
    Feb 2007
    Posts
    25

    Question

    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

  13. #13
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You just need to sort on two fields, not one.
    Try adding the prod_id to your ORDER BY statement after qtysold.
    Inspiration Through Fermentation

  14. #14
    Join Date
    Feb 2007
    Posts
    25

    Question

    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

  15. #15
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    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
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •