By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

UNION statement

P: 1
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?
Feb 19 '07 #1
Share this Question
Share on Google+
2 Replies


P: 5
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
May 23 '07 #2

Expert 100+
P: 102
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.
May 24 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.