SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select and count

    I have a db that contains prices. I want to be able to get the number for prices within 4 different ranges. I know how to do this with 4 different queries but not sure how to do it with 1 query.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT COUNT(CASE WHEN price < 10.00
                      THEN 'cheap'
                      ELSE NULL END) AS cheap_count
         , COUNT(CASE WHEN price >= 10.00
                       AND price  < 20.00
                      THEN 'modest'
                      ELSE NULL END) AS modest_count
         , COUNT(CASE WHEN price >= 20.00
                       AND price  < 30.00
                      THEN 'expensive'
                      ELSE NULL END) AS expensive_count
         , COUNT(CASE WHEN price >= 30.00
                      THEN 'ridiculous'
                      ELSE NULL END) AS ridiculous_count
      FROM prices


    Homage to "Spaceballs" for the "ridiculous count" reference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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