Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Posts
    21

    Unanswered: Counting values into separat columns

    Perhaps this is a daft question as I am new to SQL.

    Assume I have a table like this ...
    company city
    Microsoft New York
    IBM Chicago
    Red Hat Detroit
    Red Hat New York
    Red Hat San Francisco
    NVIDIA New York
    NVIDIA LA
    NVIDIA LA

    What I want as an output is ....

    company LA Detroit Chicago
    NVIDIA 2 0 0
    Red Hat 0 1 0
    .... etc

    Maybe I'm trying to do something that I shouldn't?

    The only thing I've come up with is ...
    Code:
    SELECT company, COUNT(city) as LA, 0 as Detroit, 0 as Chicago
    FROM customers
    where city = 'LA'
    GROUP BY company
    union all
    SELECT company, 0 as LA, COUNT(city) as Detroit, 0 as Chicago
    FROM customers
    where city = 'Detroit'
    GROUP BY company
    union all
    SELECT company, 0 as LA, 0 as Detroit, COUNT(city) as Chicago
    FROM customers
    where city = 'Chicago'
    GROUP BY company
    But surely there is a neater way? Especially as in my actual code I have hundred of thousands of records!

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Something like:
    Code:
    SELECT company,
           SUM(CASE WHEN CITY = 'LA' THEN 1 ELSE 0 END) as LA,
           SUM(CASE WHEN CITY = 'New York' THEN 1 ELSE 0 END) as NY,
           SUM(CASE WHEN CITY = 'Detroit' THEN 1 ELSE 0 END) as Detroit,
    etc
    FROM customers
    GROUP BY company
    

  3. #3
    Join Date
    Sep 2009
    Posts
    21
    Excellent - many thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    alternatively...
    Code:
    SELECT company
         , COUNT(CASE WHEN city = 'LA'       THEN city END) as LA
         , COUNT(CASE WHEN city = 'New York' THEN city END) as NewYork
         , COUNT(CASE WHEN city = 'Detroit'  THEN city END) as Detroit
         , ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2009
    Posts
    21
    Thanks, that does look slightly neater.

    By the way, is there a difference?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, there is a difference -- one uses SUM, the other uses COUNT

    but the results are the same!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    And one is tabbed to line up and the other isn't - it's aesthically neater.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    And one is tabbed to line up and the other isn't
    in a word, no

    it's spaced, not tabbed, but yes, it's neater

    and then, of course, mine uses COUNT instead of SUM, yeah?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2009
    Posts
    21
    Hmm thanks for those comments!

    I was meaning processing wise are they different, one may take slightly longer than the other. I don't know anything about how SQL does stuff behind the scenes, hence my question. I'd assumed they take the same time but as I'm just learning, thought I'd ask for some experts insights.

Posting Permissions

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