The original query was almost perfect – it displays the daily totals for a given month. However, specs changed and they need to add the grand total to the last row. Then another specs for the
MIN rows. Overwhelmed with the specs, I used temporary tables to make it quick.
WARNING: All queries below are not tested and will probably have typo’s or errors. All I wanted to show is how the logic is done.
The original SQL query already displays the daily totals for a given month and the new requirements were:
- Display the grand total based on the previous query and position it after the daily totals
- Display the monthly average based on the previous daily totals and position next to grand total row
- Display the monthly maximum the same way as above
- Display the monthly minimum the same way as above
What makes me decide to use a temporary table was the original query is too long that repeating the query makes it more complicated. Let’s assume that the query looks like below (simplified):
SELECT estimated_date, SUM(weight_1), SUM(weight_2) FROM table_name WHERE estimated_date BETWEEN @start_date AND @end_date GROUP BY estimated_date ORDER BY estimated_date ASC
Though the actual query is way longer than our example, they both have the same requirements. So that’s it! How do we add grand total to the last row? Add another row (average) after grand total? Maximum and minimum follows.
The only solution I could think is to use
UNION. I will create unions for grand totals, average, maximum and minimum using the same query as the first! But that means a lot of work, so let’s use temporary tables.
First, I used
SELECT INTO so that the original query’s result is inserted to the temporary table.
SELECT estimated_date, SUM(weight_1), SUM(weight_2) INTO #temp_by_month FROM table_name WHERE estimated_date BETWEEN @start_date AND @end_date GROUP BY estimated_date
Since I already get the data I need, the final query would simply query the temporary database and run aggregate functions on them. Then I’ll use
UNION to merge them all. I also added some sort of sorting key so that I can arrange the data the way I want, first the daily totals, next the grand total, next the average, next the maximum and last the minimum.
SELECT estimated_date, weight_1, weight_2 FROM ( SELECT estimated_date, weight_1, weight_2, 1 AS sort_key FROM #temp_by_month UNION SELECT 'Grand Total' AS estimated_date, SUM(weight_1) AS weight_1, SUM(weight_2) AS weight_2, 2 AS sort_key FROM #temp_by_month UNION SELECT 'Average' AS estimated_date, AVG(weight_1) AS weight_1, AVG(weight_2) AS weight_2, 3 AS sort_key FROM #temp_by_month UNION SELECT 'Max' AS estimated_date, MAX(weight_1) AS weight_1, MAX(weight_2) AS weight_2, 4 AS sort_key FROM #temp_by_month UNION SELECT 'Min' AS estimated_date, MIN(weight_1) AS weight_1, MIN(weight_2) AS weight_2, 5 AS sort_key FROM #temp_by_month ) un ORDER BY un.sort_key ASC
That’s it! Although there are more simple solution such as
GROUP BY plus
WITH ROLLUP, I prefer not to use the vendor specific syntax and use ISO compliant code, which means more work for me but less trouble in the future.