Q
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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close