#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    7
    Rep Power
    0

    Post Problem with sql count


    Hi
    the follow sql works fine but i need to also count the number of records in the tradeprice,price and rrp colums where the value is less than 1

    SELECT manufacturer,
    sum(qty*price) as pprice,
    sum(qty*rrp) as rrpprice,
    sum(qty*tradeprice) as tprice,
    sum(qty) as qtyinstock,
    count(id) as itemcount
    FROM products
    GROUP BY manufacturer
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    Code:
    SELECT manufacturer
         , SUM(qty*price) AS pprice
         , COUNT(CASE WHEN price < 1
                      THEN 'curly' END) AS low_price
         , SUM(qty*rrp) AS rrpprice
         , COUNT(CASE WHEN rrp < 1
                      THEN 'larry' END) AS low_rrp
         , SUM(qty*tradeprice) AS tprice
         , COUNT(CASE WHEN tradeprice < 1
                      THEN 'moe' END) AS low_tradeprice
         , SUM(qty) AS qtyinstock
         , COUNT(id) AS itemcount
      FROM products
    GROUP 
        BY manufacturer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    7
    Rep Power
    0
    Thanks for the quick ,clean and clear response, the solution works a treat. never really knew you could use CASE that way, you've now got me reading all about it....

    i'm very grateful for your help, many thanks

IMN logo majestic logo threadwatch logo seochat tools logo