Results 1 to 2 of 2
  1. #1
    ABW Ambassador
    Join Date
    January 18th, 2005
    Location
    ÄúsTrálíĺ
    Posts
    1,372
    I'm starting to have nightmares over this..
    I have a table which has 500 or so products all sorted into different categories, etc.

    There are a few fields, but the ones I am using for this query are prod_price and prod_listprice.

    What I want to do is run a query on this table that returns the products from a certain category (I know this bit already), but I want those results sorted in the % saving so that I can output the top 5 % savings for this category.

    Here's one I've tried, but didn't work.. I hope I'm looking in the right direction.
    SELECT * FROM shop_products WHERE prod_cat="My Category" ORDER BY ((prod_listprice-prod_price)/prod_listprice);

    I know the problem is that I have to sort the list by an actual column name and not a calculation, but maybe there is another command that will let me perform the calculation.

    I know of one solution, but that would mean adding a new column with that % saving already calculated, but that would mean changing a *lot* of code.

    any suggestions?

  2. #2
    ABW Ambassador
    Join Date
    January 18th, 2005
    Location
    ÄúsTrálíĺ
    Posts
    1,372
    OK, just in case anyone's interested, I figured it out without adding the additional column to the table.

    SELECT *,((prod_listprice-prod_price)/prod_listprice) AS val FROM shop_products WHERE prod_cat = "My Category" GROUP BY val ORDER BY val DESC

    It works, so I'm not going to mess with it anymore [img]/infopop/emoticons/icon_smile.gif[/img]

  3. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Need help with MySQL query
    By mobilebadboy in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: January 17th, 2007, 03:00 AM
  2. MySql help?
    By bsnrjones in forum Programming / Datafeeds / Tools
    Replies: 7
    Last Post: March 5th, 2004, 01:47 PM
  3. MySQL
    By Dirk in forum Midnight Cafe'
    Replies: 4
    Last Post: February 21st, 2004, 05:44 PM
  4. mysql sl-o-o-o-o-o-o-w
    By Heyder in forum Programming / Datafeeds / Tools
    Replies: 4
    Last Post: December 20th, 2002, 06:47 PM

Posting Permissions

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