Getting 2 Counts from one field.

[bryandj23]

Hi all.

I have a database of telephone system records.

I'm having a bit of trouble trying to come up with a SELECT statement for getting the count of incoming calls and the count of outgoing calls.

Basically, I want a report by Extension number, showing the average length of calls, number of incoming calls and number of outgoing calls. Incoming calls have "IN" in a field called "CalledNumber"

Something along the lines of:

SELECT Extension, AVG(Duration), Count(CalledNumber)

This is where I'm stuck. How can I say "count of called number where callednumber = 'IN' AND called of called number where callednumber <> 'IN'

Thanks in advance

 

[Crowley16]

in this case, I'd use a subquery with groupings on the callednumber field.

if you put groupings on your main query, then that'll force grouping results against your other fields...

--------------------
Procrastinate Now!

 

[gmmastros]

Something like this..

SELECT Extension, AVG(Duration), [!]Sum(Case When CalledNumber = 'in' Then 1 Else 0 End)[/!] As CountOfIncomingCalls

-George

"the screen with the little boxes in the window." - Moron

 

[AlexCuse]

I think this will work for you:

[COLOR=blue]select[/color] extension, [COLOR=#FF00FF]avg[/color](duration)
, sum([COLOR=blue]case[/color] [COLOR=blue]when[/color] CalledNumber = [COLOR=red]'IN'[/color] [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) [COLOR=blue]as[/color] [Incoming [COLOR=#FF00FF]Count[/color]]
, sum([COLOR=blue]case[/color] [COLOR=blue]when[/color] CalledNumber = [COLOR=red]'IN'[/color] [COLOR=blue]then[/color] 0 [COLOR=blue]else[/color] 1 [COLOR=blue]end[/color]) [COLOR=blue]as[/color] [Outgoing [COLOR=#FF00FF]Count[/color]] 
[COLOR=blue]from[/color] myTable
[COLOR=blue]group[/color] [COLOR=blue]by[/color] extension

Take a look at CASE function in BOL (SQL Server Help Files). I think you will find it most helpful.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Slow clicker finger today :-(

Ignorance of certain subjects is a great part of wisdom

 

[bryandj23]

Alex...

Thank you so very much!! Working without a hitch.

Also, thanks for the reference to the CASE function. I am slowly learning more and more SQL. I know only the basics, and learning as I go.

I appreciate you taking the time.