Help with subquery logic please 1

[Malekish]

I know I've done this in the past but I haven't done it in a number of years. The answer is in my head but I just can't remember how to do it.

Desired output:

UserID TotalCalls ShortCalls
100 200 10
101 210 50
102 150 6

Example of source data
UserID Start_Time Duration
100 1/1/09 10:00 50
100 1/2/09 15:00 8
101 1/5/09 08:00 25

So, the TotalCalls is just a count(), and I define "ShortCalls" as WHERE Duration between 1 and 10 (because there are 0 duration calls)

So, I have 2 queries, one to group UserID and TotalCalls, the other I'm stuck on.

Do I do the subquery as a virtual table in my from statement, if so what is the syntax to join the two together. Do I do the subquery to refine what calls I count for the SDCalls field?

Man, I'm so close to the answer but I'm stuck on just that last part, if someone can give me a nudge in the right direction I'd appreciate the help.

 

[r937]

SELECT UserID
     , COUNT(*) AS TotalCalls
     , COUNT(
          CASE WHEN Duration BETWEEN 1 AND 10  
               THEN 'short' ELSE NULL END
            )   AS ShortCalls
  FROM daTable
GROUP
    BY UserID

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Malekish]

...

Wow, what a great solution. Thank you! (and looking at your book as an option on Amazon)