I wrote the below query comparing the output between using and not using NVL(counter,0). I'm getting different values for some corresponding rows.
Can't see where I'm going wrong. Will appreciate if someone could help.
ROUND(SUM(COUNTER1+COUNTER2)/AVG(COUNTER3),2) as ABC,
ROUND(SUM(NVL(COUNTER1,0)+NVL(COUNTER2,0))/AVG(NVL(COUNTER3,0)),2) as ABC1
DATETIME BETWEEN TO_DATE('11-02-10 00:00', 'dd-mm-yy hh24:mi') AND TO_DATE('11-02-10 23:59', 'dd-mm-yy hh24:mi')
GROUP by datetime)
most aggregate functions (SUM and AVG among them) ignore NULL. It affects AVG, as AVG(2,4,NULL)=3 and AVG(2,4,0)=2.
You might easily confirm it by running partial results (and knowing, that AVG=SUM/CNT):
SELECT SUM(COUNTER3), COUNT(COUNTER3), AVG(COUNTER3),
COUNT(DECODE(COUNTER3,NULL,1)), -- number of NULLs in COUNTER3
SUM(NVL(COUNTER3,0)), COUNT(NVL(COUNTER3,0)), AVG(NVL(COUNTER3,0))
WHERE <condition on datetime>;
The "correct" result depends on requirements, of which nobody except you is aware.
AVG(COUNTER3) and AVG(NVL(COUNTER3,0)) are not the same. And the NULL fields are not being taken into account when the AVG is being computed. However, if the NULL field is replaced with a zero, then the field is being taken into account.