Results 1 to 3 of 3

Thread: Totals Query

  1. #1
    Join Date
    Sep 2003
    Posts
    30

    Angry Unanswered: Totals Query

    I am having some trouble writing this query. The raw data looks something like this:

    Serial CallType
    12345 1
    10254 1
    10145 E
    12345 1
    12345 2
    12345 X

    And the results I am looking for are

    Serial CountType1 CountType2 CountTypeE CountTypeX
    12345 2 1 0 1
    10254 1 0 0 0
    10145 0 0 1 0

    I guess I am just having a brain cramp. I know tha I could create a subquery for each Call Type, but there are over 10 different types, so I was wondering if there was a way to do this on the fly.

    Thanks for any help,

    Rob

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select Serial
         , SUM(IIF(CallType='1'),1,0)) as CountType1
         , SUM(IIF(CallType='2'),1,0)) as CountType2 
         , SUM(IIF(CallType='E'),1,0)) as CountTypeE 
         , SUM(IIF(CallType='X'),1,0)) as CountTypeX
      from foo
    group
        by Serial
    extend as required
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Posts
    30
    As I bang my head against the desk for not trying that, I Thank you very much.

Posting Permissions

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