Home » SQL & PL/SQL » SQL & PL/SQL » Average getting calculated incorrectly using group by grouping sets
Average getting calculated incorrectly using group by grouping sets [message #188625] Mon, 21 August 2006 01:15 Go to next message
zulu99
Messages: 4
Registered: August 2006
Junior Member
Hi All

I am using the group by grouping sets to calulate the running totals of aggregate functions like count, sum & avg.

I get the grand totals of count & sum perfectly right but with avg i am getting the averages incorrectly as the number of rows retrieved by the select query increases.
With few rows (3-4) the averages are round about correct but when the number of rows retreived increase say 5 or more than 5 I get absurd average figures

Here is the query I am using

<code>

select AGENT.NAME,
sum(decode(ACTIVITY.status,'New',1)) cnt,
sum(decode(ACTIVITY.status,'Open',1)) sumopen ,
round(avg( decode (ACTIVITY.status,'Open',ACTIVITY.STATUS_DIFF)),0) avgopen,
sum(decode(ACTIVITY.status,'Sent',1)) sumsent,
round(avg( decode (ACTIVITY.status,'Sent',ACTIVITY.STATUS_DIFF)),0) avgsent,
sum(decode(ACTIVITY.status,'Dead',1)) sumdead,
round(avg( decode (ACTIVITY.status,'Dead',ACTIVITY.STATUS_DIFF)),0) avgdead
from ACTIVITY, REQUEST , AGENT
where
REQUEST.DEALER_NUMBER = AGENT.DEALER_ID
and REQUEST.CONFIRMATION='Y'
and REQUEST.REQUEST_ID = ACTIVITY.REQUEST_ID
and REQUEST.REQUEST_TYPE='B'
and ACTIVITY.ACTIVITY_DATE between to_date('2006-01-01 ','YYYY-MM-DD') and to_date('2006-08-31','YYYY-MM-DD')
group by grouping sets((AGENT.DEALER_ID,AGENT.NAME), ())
</code>

Do let me know your suggestions


Regards
Re: Average getting calculated incorrectly using group by grouping sets [message #188666 is a reply to message #188625] Mon, 21 August 2006 03:02 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As a mathematician, I like this line:
Quote:

With few rows (3-4) the averages are round about correct

Either the averages are right, or they're wrong. There are no other options.

What I suspect is happening is this:
AVG sums all the values it is given, and then divides by the number of none null values
SQL> select avg(col_1)
  2  from  (select 1 col_1 from dual
  3        union all
  4         select null col_1 from dual);

AVG(COL_1)
----------
         1


Either that, or the GROUPING SETS is doing things that you aren't expecting.

Why don't you show us somew sample data, and the results that are generated, and we'll explain what's happening.
Previous Topic: data type issue
Next Topic: Conversion of VARCHAR2 to XMLTYPE possible?
Goto Forum:
  


Current Time: Tue Dec 06 06:39:52 CST 2016

Total time taken to generate the page: 0.06818 seconds