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.