×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help with subquery logic please

Help with subquery logic please

Help with subquery logic please

(OP)
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.

RE: Help with subquery logic please

CODE

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
smile

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

RE: Help with subquery logic please

(OP)
...

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close