UNION statement

[wishlister]

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?

[TertiaryKey]

You should be able to achieve this by replacing the nested column function with a sub-query - maybe something like 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((SELECT SUM(T.GROSS)-A.EXPENSE AS INCOME
FROM EMP A, TEMP T
WHERE A.EID=T.TID
GROUP BY A.EXPENSE )) AS INCOME
FROM EMP A, TEMP T
WHERE A.EID=T.TID
GROUP BY A.EXPENSE

[pradeep kaltari]

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?

Hi,
Could you please explain why do you want to do AVG(SUM(...)) in your second SELECT statement?

Regards,
Pradeep.