DB ESE
i have a query that will compute values using SUM() and it will produce more than one row. on the last row, i want to compute for its average. can someone give me an example on what should i do? just an example.. tnx!

i tried this..
SELECT SUM(T.GROSS)-A.EXPENSE AS INCOME
FROM EMP A, TEMP T
WHERE A.EID=T.TID
GROUP BY A.EXPENSE
UNION
SELECT AVG(SUM(T.GROSS)-A.EXPENSE) AS INCOME
FROM EMP A, TEMP T
WHERE A.EID=T.TID
GROUP BY A.EXPENSE

but it didn't work because nested column functions is not allowed. so, how will i fix it?

try a nested table expression:

SELECT SUM(T.GROSS)-A.EXPENSE AS INCOME
FROM EMP A, TEMP T
WHERE A.EID=T.TID
GROUP BY A.EXPENSE
UNION ALL
SELECT AVG(INCOME) FROM
( SELECT SUM(T.GROSS)-A.EXPENSE AS INCOME
FROM EMP A, TEMP T
WHERE A.EID=T.TID
GROUP BY A.EXPENSE ) X

( btw: UNION without ALL eliminates duplicate rows, which results in a sort -> the average will not appear as the last row )

i've tried that but it says...
An unexpected token "" was found following ")". Expected tokens may
include: "AS".
... that i can't see and understand

oic.. i never thought X is included! so, may i know what it is for? I mean, its essence to prevent the error i got? tnx a lot!

Try creating query like this
select x.* from (
select '1' as inum,sum(T1.TB_STATS_ROWS_WRITE) as first_write,sum(T1.TB_STATS_ROWS_READ) as first_read,
T1.TB_STATS_TABLE,T1.TB_STAT_DBNAME,T1.TB_STATS_SC HEMA
from TB_STATS_HISTORY T1
where
month(T1.TB_STAT_TS) = month(current date - 1 month)
and day(T1.TB_STAT_TS) between 1 and 7
group by T1.TB_STATS_TABLE,T1.TB_STAT_DBNAME,T1.TB_STATS_SC HEMA
Union all
select '2' as inum,sum(T1.TB_STATS_ROWS_WRITE) as first_write,sum(T1.TB_STATS_ROWS_READ) as second_read,
T1.TB_STATS_TABLE,T1.TB_STAT_DBNAME,T1.TB_STATS_SC HEMA
from TB_STATS_HISTORY T1
where
month(T1.TB_STAT_TS) = month(current date - 1 month)
and day(T1.TB_STAT_TS) between 1 and 7
group by T1.TB_STATS_TABLE,T1.TB_STAT_DBNAME,T1.TB_STATS_SC HEMA
) as x
order by x.inum,x.TB_STAT_DBNAME";

I hope this helps

I created the temporary table and dumped the values in the temporary table and sorted the values.

Regards
Rajesh Krishnan

this is for syntax reasons only.
It doesn't matter what you code here ( you may write X or HUGO or THISISALONGNAME or whatever you want ), but you have to code at least one character.