Create Table #Repair(
Column1 int, Column2 int, Column3 int,
);
Insert Into #Repair VALUES( 1, 2, 3 );
Insert Into #Repair VALUES( 3, 4, 5 );
SELECT Column1, Column2, Column3
FROM #Repair
ORDER BY Column1
COMPUTE SUM( Column2 );
-- Output of SELECT statement using the COMPUTE clause
-- Please note that this generates multiple resultsets.
/*
Column1 Column2 Column3
----------- ----------- -----------
1 2 3
3 4 5
sum
===========
6
*/
-- It requires more programming to handle the multiple
-- resultsets generated by COMPUTE & it gets messy if
-- you are calculating values based on multiple columns.
-- Hence, the alternative SELECT approach using ROLLUP.
SELECT Column1, Column2, Column3 , SUM( Column2 ) AS Overall_Sum
FROM #Repair
GROUP BY Column1 , Column2 , Column3
WITH ROLLUP
HAVING (
GROUPING( Column1 ) = 0 And
GROUPING( Column2 ) = 0 And
GROUPING( Column3 ) = 0
) or
(
GROUPING( Column1 ) = 1 And
GROUPING( Column2 ) = 1 And
GROUPING( Column3 ) = 1
)
-- Use ORDER BY to push the summary / overal sum row to the end
ORDER BY COALESCE( Column1 , 2147483647 /* Maximum INTEGER value */);
-- Output of SELECT statement:
/*
Column1 Column2 Column3 Overall_Sum
----------- ----------- ----------- -----------
1 2 3 2
3 4 5 4
NULL NULL NULL 6
*/
This page was last updated on May 01, 2006 04:28 PM.