January 20, 2009 at 2:22 am
Can anyone advice how to join results from two (or more) SELECTs with aggregate function?
Select 1
SELECT
t1.column, t2.column, SUM(t3.column)
FROM
table1 t1 JOIN table2 t2 JOIN table3 t3
GROUP BY
t1.column, t2.column WITH ROLLUP
Select 2
SELECT
t1.column, t2.column, SUM(t4.column)
FROM
table1 t1 JOIN table2 t2 JOIN table4 t4
GROUP BY
t1.column, t2.column WITH ROLLUP
A final report should look like this:
t1.column, t2.column, SUM(t3.column), SUM(t4.column), SUM(t3.column) - SUM(t4.column)
January 20, 2009 at 2:51 am
Your post is not very clear because it is hard to know if both queries produces the same values in the common columns (the columns that come from table1 and table2). It is also not clear what to do if they produce different set of records (for example if table4 has no records at all, then the second query will produce no records at all). I recommend that you’ll use the link in my signature to learn how to post questions in a better way that will also help you get better answers and get those answers faster.
As for your question – here is one way of doing something similare to what you asked. Since your post was not very clear, you’ll might need to use outer join between the CTEs and if the values of col1 and col2 will be exactly the same you can use just one CTE:
with MyCTE1 as (
select t1.col1, t2.col2, sum(t3.col3) as SumT3
from table1 t1 inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t1.id = t3.id
group by t1.col1, t2.col1 WITH ROLLUP),
MyCTE2 as (
select t1.col1, t2.col2, sum(t4.col3) as SumT4
from table1 t1 inner join table2 t2 on t1.id = t2.id
inner join table4 t4 on t1.id = t4.id
group by t1.col1, t2.col1 WITH ROLLUP)
select MyCTE1.t1, MyCTE1.t2, SumT3, SumT4, SumT3-SumT4
from MyCTE1 inner join MyCTE2 on MyCTE1.t1 = MyCTE2.t1 and MyCTE1.t2=MyCTE2.ts
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 20, 2009 at 3:46 am
Adi, CTEs are exactly a technique I was looking for. Thank you, your help is much appreciated.
I promise the next time I will do my best to conform to your list of best practices when posting a message to the forum.
BTW - all your assumptions regarding my query are correct.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply