Adding together results of two subqueries

[mayB2morrow]

I am doing a rather complicated query which includes within it, two subqueries.

The two sub-queries calculate two individual values.

I need to add a third column to the query which will show these two values added together, but can't seem to achieve this. Any help would be most appreciated. See query code below...



SELECT USRCODE, IDUSER AS UserID, Chargeable + NonChargeable AS Total,
(SELECT SUM(dbo.ACT.DURATION)
FROM dbo.ACT RIGHT OUTER JOIN
dbo.ACT_USR ON dbo.ACT.IDACTION = dbo.ACT_USR.IDACTION LEFT OUTER JOIN
dbo.PROJ INNER JOIN
dbo.PROJ_ACT ON dbo.PROJ.IDPROJECT = dbo.PROJ_ACT.IDPROJECT ON
dbo.ACT.IDACTION = dbo.PROJ_ACT.IDACTION
WHERE (dbo.PROJ.FLD91003 = 'ORDER') AND (dbo.ACT_USR.KIND = '0') AND (dbo.ACT.KIND = '1')
GROUP BY dbo.ACT_USR.IDUSER, dbo.ACT.CHARGEABLE
HAVING (dbo.ACT.CHARGEABLE = '1') AND (dbo.ACT_USR.IDUSER = dbo.users.iduser)) AS Chargeable,
(SELECT SUM(dbo.ACT.DURATION)
FROM dbo.ACT RIGHT OUTER JOIN
dbo.ACT_USR ON dbo.ACT.IDACTION = dbo.ACT_USR.IDACTION LEFT OUTER JOIN
dbo.PROJ INNER JOIN
dbo.PROJ_ACT ON dbo.PROJ.IDPROJECT = dbo.PROJ_ACT.IDPROJECT ON
dbo.ACT.IDACTION = dbo.PROJ_ACT.IDACTION
WHERE (dbo.PROJ.FLD91003 = 'ORDER') AND (dbo.ACT_USR.KIND = '0') AND (dbo.ACT.KIND = '1')
GROUP BY dbo.ACT_USR.IDUSER, dbo.ACT.CHARGEABLE
HAVING (dbo.ACT.CHARGEABLE = '0') AND (dbo.ACT_USR.IDUSER = dbo.users.iduser)) AS NonChargeable
FROM dbo.USERS
GROUP BY USRCODE, IDUSER, USRCODE

 

[r937]

you cannot use a column alias like that, you actually have to repeat the subquery

which, as you can imagine, is rather messy

i would recommend you define a view for the subquery (note: those joins are really wonky, can you really have two ON clauses in a row llike that? and whoa, i never mis RIGHT OUTERs with LEFT OUTERs like that, it's just too confusing)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[chiph]

Method 1:
Declare two variables to hold the results of your two SELECTS, and you can add them together to form your third column.

Method 2:
Wrap your current outer SELECT in yet another SELECT which adds together the two columns you want.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first