COMPUTE Clause in Microsoft SQL Server 2005 for sub total:

So far sub-total was very tedious task to do but with COMPUTE clause in SQL-Server 2005 it became fun. Don’t you believe me? Let me take you towards COMPUTER and you will believe me for sure.

COMPUTE clause is nothing but the aggregate query tacked on to the end of a normal query. This query simply returns the simple detail rows with the specific aggregate summary for that result set only.

Let us see it practically.

--CREATE on table for demonstration

create table BookAuthor

(

AuthorName VARCHAR(50),

BookCategory VARCHAR(10),

TotalBook INT

)

--Insert some records

INSERT INTO BookAuthor

SELECT 'A','C LANG',2 UNION ALL

SELECT 'B','C LANG',3 UNION ALL

SELECT 'C','SQL',2 UNION ALL

SELECT 'A','SQL',4

After creating above table and insert the records. What will we do??? If we wish to find which author wrote book for which category and total number of book written for every book category. Well, we have age old solution for this but I am going to show your new approach by COMPUTE clause.

SELECT AuthorName,BookCategory,TotalBook

FROM BookAuthor

ORDER BY BookCategory

COMPUTE Sum(totalBook)

BY BookCategory

Above query will return you all rows from table BookAuthor and will show your total per Book Category. Remember if you wish to use book category as group by (in BY clause) in COMPUTE clause you have to define it in order by of simple SELECT query as well.

If you will not mention Book Category in Order By clause of SELECT statement, you will be greeted by following error.

Msg 143, Level 15, State 1, Line 2

A COMPUTE BY item was not found in the order by list. All expressions in the compute by list must also be present in the order by list.

Reference: Ritesh Shah