Issue
I have a question regarding structuring a query. I have 5 tables in which I need particular data from or data that needs to be manipulated from. I need to make calculations based upon the data in these tables, but first I need to display the columns with the data that I do not need to make any calculations on. What I have done so far is I have made a temporary table and queried all of the columns as needed into that table. My next task and thought process is to make another temporary table and do all of the calculations and fill them in, then join both temporary tables into one table.
My problem: For all of these calculations, some require an average based calculation, count, sum, etc which in total will have at least 10 new columns. The issue I am having is structuring a query that will allow me to achieve these calculations from the table(s) and to achieve my desired results. Can anyone help aid in this complex query and structure of it?
Solution
A sample of the data might be helpful. I would suggest using T-SQL’s Window functions in order to do the aggregations, if applicable. Window functions allow you to do these aggregations without having to do multiple GROUP BY’s. This is very helpful when the data needs to be aggregated over different groups as it allows you to do all of the aggregations in one query rather than having to write one query rather than having to write several queries, all with different GROUP BY’s.
Here is a link to basic Windowing Functions in T-SQL.
https://msdn.microsoft.com/en-us/library/ms189461.aspx
Common Table Expressions (CTE’s) are also very useful when doing multiple aggregations over datasets, as they allow you to better logically order your queries for readability. Each CTE you create can reference any CTE created above it, which allows you to chain multiple of them together before you produce your final output.
Here is a link to some basic CTE examples (see Example C for using Multiple CTE’s).
https://msdn.microsoft.com/en-us/library/ms175972.aspx
Answered By – rhholt
Answer Checked By – Katrina (BugsFixing Volunteer)