EasyCFM.COM ColdFusion Forums / MySQL Related Issues / exclude zeros from avg() in group by query

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: exclude zeros from avg() in group by query

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

Lossed
09-05-2007 @ 10:29 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Hi all,

I'm stumped on this.

Say a table has three columns: sportID, distance, elapsedTime.
Each are integer data types.

I'd like to get the average distance and elapsedTime per sport:

select
    sportID,
    avg(distance),
    avg(elapsedTime)
from table
group by sportID

But...the value of each individual distance or elapsedTime row could be 0 and I want to exclude those from the avg(). For example, there may be 20 rows making up the first sportID group, but only one of them has a distance value, say, 200. The avg(distance) is going to show 10 (200/20), when I want to return 200 (200/1).

I can do this with multiple queries, and outside the query, but am wonder how to contain such inside the one query, if possible.

It's doing my head in!

Is there a way to contain this in the query, in a single connection, or at least a way I can count the non-zero distance and elapsedTime values per sport so I can then work out averages later?

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

This message was edited by Lossed on 9-6-07 @ 4:47 AM

Lossed
09-05-2007 @ 11:27 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

OK, fess up. Who knew we could use null and conditional statements inside the aggregate functions? I may just need to get out more, b/c I think that's...like...way cool...dude:

avg(
    if(elapsedTime <>0,
        elapsedTime,
    null)) as avgElapsedTime

:)

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------


Website Designed and Developed by Pablo Varando.