HELP WITH SQL!

[tsyle]

I have this one Table with information about Calls_Completed and Call_Duration. Each row in the Calls_Completed Column counts as 1 call, so i used a count statement to count each row for that and it works, but my problem is trying to count Call_Duration. In this column each unsuccessful call is equal to 0, so I setup a statement that queries all the Call_Duration that are not 0. The problem with this is that all the calls that has failed will not query. Let me show you.

Date Location TotalTime Duration
4/21/2005 USA 11 1
4/21/2005 Canada 12 0
4/21/2005 USA 3 5

Each row counts as a call, and each call in Duration that is not equal to 0 is consider a successful call.

So, USA has 2 Total Calls and 2 Completed Calls while Canada has 1 Completed Call and 0 Completed call.

I want it to show up like this in my query

Date Location Total_Calls Call_Completed
4/21/2005 USA 2 2
4/21/2005 Canada 1 0

Any idea how to do this? I tried using a count(*) statement and i ended up with this

Date Location Total_Calls Call_Completed
4/21/2005 USA 2 2

I used count only if calls does not = 0, which completed ignored Canada because it has a 0.

 

[PHV]

Something like this ?
SELECT Date, Location, Count(*) Total_Calls
, Sum(CASE WHEN Duration=0 THEN 0 ELSE 1 END) Call_Completed
FROM yourTable
GROUP BY Date, Location

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tsyle]

I tried running that in Access and its giving me errors.

This is what i am using:

SELECT CDR.[Login Date], CDR.[Outbound IP], CDR.ID1, CDR.Country, Count(*) AS [Competed Calls], Sum(CASE WHEN CDR.Duration = 0 THEN 0 ELSE 1 END) AS SumOfDuration
FROM CDR
GROUP BY CDR.[Login Date], CDR.[Outbound IP], CDR.ID1, CDR.Country;

Its telling me that there is a Syntax error (missing operator) in query expression 'Sum(CASE WHEN CDR.Duration = 0 THEN 0 ELSE 1 END)'

 

[PHV]

You asked in the ANSI SQL forum, so you got an ANSI answer.
For msaccess, you may try this:
SELECT CDR.[Login Date], CDR.[Outbound IP], CDR.ID1, CDR.Country, Count(*) AS Total_Calls, Sum(IIf([Duration]=0, 0, 1)) AS Call_Completed
FROM CDR
GROUP BY CDR.[Login Date], CDR.[Outbound IP], CDR.ID1, CDR.Country;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tsyle]

Ohhh... there are different SQL?... Sorry i'm new to this... but THANK YOU for the help. I have been stuck on this for an entire day.

 

[r937]

Ohhh... there are different SQL?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)