×
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 BY problem with JOINs

ORDER BY problem with JOINs

ORDER BY problem with JOINs

(OP)
Here is my problem and it appears that it may only be a problem with PostgreSQL:

I am trying to order the products on my online store showing the products that have been sold the most amount of times within the last week on the top.  

There are a lot of products that may not have been sold within the last week, so may not appear in the orders_prods table.

This is how I want them to be ordered:

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

 

Here is my current query:

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 op.quantitySold ASC

This Query orders the products like this:

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


When I order quantitySold by DESC, the products that have been sold recently always appear on the bottom no matter what I try.

Here is an example query:

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 op.quantitySold DESC, p.prod_id ASC

And I get this result:

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


If anyone has any idea why this is happening, I would greatly appreciate the help.

      Thank You,
                    Kyle

RE: ORDER BY problem with JOINs

actually at first glance I don't see any reason, but why do you do that

SELECT op.quantitySold,  ..... 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

instead of

SELECT (SELECT SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' WHERE prod_id = p.prod_id) AS quantitySold ....



(as an idea try ORDER BY quantitySold - without the op.)

RE: ORDER BY problem with JOINs

(OP)
Thanks for the help ceco,

      I am sorry, but I posted to so many forums that I forgot to put my final answer on this forum.  The conclusion that I came to (with a lot of help from a couple of forums) is this final query.  And it works great and seems to execute very quickly.  What I forgot is that postgreSQL treats null values differently than zero, so I needed to use the COALESCE function.

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 COALESCE(op.quantitySold, 0) ASC


      Take care,
             Kyle

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