Problem solve
Get help with specific problems with your technologies, process and projects.
Joining derived tables
I have three tables -- table1, table2, and table3. All three contain information on a daily basis. I have written SQL statements to summarize the information of each table based on certain columns in the respective tables. Can I write an SQL statement where in it should perform the summarization of each of the three tables separately and then the summarized results be joined together using where clause? The SQL statements should not create any individual summary tables.
Use a derived table for each query, and join them on their common grouping column. For example, if the grouping column is userid, you would use this:
select dt1.userid , dt1.sumcol1 as table1_sumcol1 , dt2.sumcol1 as table2_sumcol1 , dt3.sumcol1 as table3_sumcol1 from ( select userid , sum(col1) as sumcol1 from table1 group by userid ) as dt1 inner join ( select userid , sum(col1) as sumcol1 from table2 group by userid ) as dt2 on dt1.userid = dt2.userid inner join ( select userid , sum(col1) as sumcol1 from table3 group by userid ) as dt3 on dt1.userid = dt3.userid
Notice how the derived tables each have an alias. Also, in the SELECT list of the outer query, notice that column aliases are needed to distinguish the columns from each derived table.
This was last published in December 2004
Dig Deeper on SQL Server Database Modeling and Design
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our SQL Server experts
View all SQL Server questions and answers
Start the conversation
0 comments