Thread: SQL query help

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

    Join Date
    Nov 2008
    Posts
    72
    Rep Power
    6

    SQL query help


    Is it possible to get the following from a SQL string?

    Code:
    sample table
    model        make            location
    ford           f150               1
    chevy        camaro              1
    ford           f150               2
    ford           f150               3
    chevy        camaro               1
    ford           ranger             3
    
    Can you do a SQL string that would returns records like below?
    model        make       Count1               Count2     Count3
    ford           f150          1                   1           1
    chevy        camaro          2                  0           0
    ford           ranger        0                  0           1
    Where the count1, 2, 3 are the count of the make at each location.

    Didn't think this was possible with a SQL string but thought I would check. Thanks for any help.
    Last edited by wakerunner; July 11th, 2011 at 10:46 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    SELECT model
         , make
         , COUNT(CASE WHEN location = 1
                      THEN location END ) AS Count1
         , COUNT(CASE WHEN location = 2
                      THEN location END ) AS Count2
         , COUNT(CASE WHEN location = 3
                      THEN location END ) AS Count3
      FROM daTable
    GROUP
        BY model
         , make

    Comments on this post

    • wakerunner agrees : Terrific and amazing stuff
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    72
    Rep Power
    6
    Incredible! The more I learn about SQL the more amazed I am. BTW your simply-sql.com site does not have any links on it to purchase your book (unless I missed something... I don't see any links at all just a static page), I do see it on Amazon though, I will definitely buy it on Amazon unless you have a direct way to buy. Thanks for your help!

    Originally Posted by r937
    Code:
    SELECT model
         , make
         , COUNT(CASE WHEN location = 1
                      THEN location END ) AS Count1
         , COUNT(CASE WHEN location = 2
                      THEN location END ) AS Count2
         , COUNT(CASE WHEN location = 3
                      THEN location END ) AS Count3
      FROM daTable
    GROUP
        BY model
         , make
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by wakerunner
    BTW your simply-sql.com site does not have any links on it to purchase your book (unless I missed something... I don't see any links at all just a static page),
    yes, it's just a static page for the time being (long story)

    amazon or your local bookstore is fine

    feel free to contact me if there's anything in the book that isn't clear

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo