469,355 Members | 2,547 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,355 developers. It's quick & easy.

UNION statement

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
2 8486
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
pradeep kaltari
102 Expert 100+
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.

Similar topics

5 posts views Thread by Thomas Baxter | last post: by
3 posts views Thread by Paradigm | last post: by
4 posts views Thread by Elroyskimms | last post: by
2 posts views Thread by MC | last post: by
4 posts views Thread by Girish | last post: by
2 posts views Thread by jafastinger | last post: by
10 posts views Thread by Troels Arvin | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.