SQL query help

Dev Shed Forums Sponsor:

July 11th, 2011, 10:44 PM

wakerunner

Contributing User

Join Date: Nov 2008

Posts: 68

Time spent in forums: 12 h 49 m
Reputation Power: 5

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.

July 12th, 2011, 01:22 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 32 m 9 sec
Reputation Power: 4140

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

July 12th, 2011, 08:30 AM

wakerunner

Contributing User

Join Date: Nov 2008

Posts: 68

Time spent in forums: 12 h 49 m
Reputation Power: 5

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!

Quote:

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

July 12th, 2011, 08:57 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 32 m 9 sec
Reputation Power: 4140

Quote:

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

Developer Shed Advertisers and Affiliates