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.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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 first published in December 2004

Back to top