Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

COMPUTE: now it is going, what else will do it?

/*I'd be very surprised if you've ever used the old COMPUTE clause in SQL Server. It is a bit mad, and it is flagged for deprecation. It is an old Sybase legacy that was used when reports were printed directly from the output of SQL. You can still do it, but the world has really moved on. You can't use it for much else, because the output consists of a mass of separate results, and the only way you'd ever sensibly catch that lot is from the output of SQLCMD, and even then you'd have to parse it back into something that you could manipulate. Don't even think about it. It was great for directly printing out in a monospace font to a printer. In those days, you'd have just sent the output to the printer with a >PRN and then you'd be laughing.

Here is an example of its use, with AdventureWorks...*/

USE AdventureWorks;
GO
SELECT name, totalDue, OrderMonth  FROM (
SELECT     [name]=CONVERT(VARCHAR(12),RIGHT(HumanResources.Employee.LoginID, CHARINDEX('\',REVERSE(HumanResources.Employee.LoginID)+'\')-1)),
          
[orderMonth]=CONVERT(DATETIME,'1 '+RIGHT(CONVERT(CHAR(11),Sales.SalesOrderHeader.OrderDate,113),8)),
          
Sales.SalesOrderHeader.TotalDue
FROM         HumanResources.Employee INNER JOIN
                      
Sales.SalesPerson ON Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID INNER JOIN
                      
Sales.SalesOrderHeader ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
              
)salesReport
ORDER BY    OrderMonth, name                  
COMPUTE SUM(totalDue) BY OrderMonth,name
/* which gives...

name         totalDue              OrderMonth
------------ --------------------- -----------------------
david8       19005.2087            2001-07-01 00:00:00.000
david8       974.0229              2001-07-01 00:00:00.000
david8       10784.9873            2001-07-01 00:00:00.000
david8       56729.9942            2001-07-01 00:00:00.000
david8       4592.3356             2001-07-01 00:00:00.000

sum
---------------------
92086.5487

name         totalDue              OrderMonth
------------ --------------------- -----------------------
garrett1     10319.3341            2001-07-01 00:00:00.000
garrett1     1737.6562             2001-07-01 00:00:00.000

sum
---------------------
12056.9903

name         totalDue              OrderMonth
------------ --------------------- -----------------------
jillian0     45187.5136            2001-07-01 00:00:00.000
jillian0     7553.7239             2001-07-01 00:00:00.000
jillian0     8095.7863             2001-07-01 00:00:00.000
jillian0     1159.9768             2001-07-01 00:00:00.000

sum
---------------------
61997.0006

...etc...


Now, the documentation says that you can use ROLLUP to do the same thing but doesn't say how. You can get almost the same result, although nobody in their right minds would want it nowadays. A more useful report would give the monthly subtotals for each member of the sales team, the total for the month, and the grand total. Here is that more useful report, but you should be warned that the NULL values mean 'total' rather than unknown, and you would do well to use the GROUPING() function to do the fancy formatting at the application end of the process. The result is odd, since it has an order, and if you insert the results into a table, it is a good idea to put a row-order value in it.*/
SELECT  name,
      
totalDue,
      
RIGHT(CONVERT(CHAR(11),Ordermonth,113),8),
      
SUM(totaldue) AS total  FROM (
SELECT     [name]=CONVERT(VARCHAR(12),RIGHT(HumanResources.Employee.LoginID, CHARINDEX('\',REVERSE(HumanResources.Employee.LoginID)+'\')-1)),
          
[orderMonth]=CONVERT(DATETIME,'1 '+RIGHT(CONVERT(CHAR(11),Sales.SalesOrderHeader.OrderDate,113),8)),
          
Sales.SalesOrderHeader.TotalDue
FROM         HumanResources.Employee INNER JOIN
                      
Sales.SalesPerson ON Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID INNER JOIN
                      
Sales.SalesOrderHeader ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
              
)salesReport
GROUP BY ordermonth,name,totalDue
WITH rollup

/*
name         totalDue                       total
------------ --------------------- -------- ---------------------
david8       974.0229              Jul 2001 974.0229
david8       4592.3356             Jul 2001 4592.3356
david8       10784.9873            Jul 2001 10784.9873
david8       19005.2087            Jul 2001 19005.2087
david8       56729.9942            Jul 2001 56729.9942
david8       NULL                  Jul 2001 92086.5487
garrett1     1737.6562             Jul 2001 1737.6562
garrett1     10319.3341            Jul 2001 10319.3341
garrett1     NULL                  Jul 2001 12056.9903
jillian0     1159.9768             Jul 2001 1159.9768
jillian0     7553.7239             Jul 2001 7553.7239
jillian0     8095.7863             Jul 2001 8095.7863
jillian0     45187.5136            Jul 2001 45187.5136
jillian0     NULL                  Jul 2001 61997.0006
...etc...


You'll see that there is an extra column for totals, but there is a certain logic to it.
The last lines look like this

tsvi0        NULL                  Jun 2004 309113.7562
NULL         NULL                  Jun 2004 4573990.91
NULL         NULL                  NULL     108266245.7018

The first one shows the total for tsvi0 for June 2004 as being 309113.7562
the next line shows the entire total sales for June as being 4573990.91
the final line shows the grand total being 108266245.7018

As a final thought, it is actually very easy to process a report like this to provide HTML or XHTML output to then render it on a browser, and print out from the browser. It is quick to do once you've got the SQL set up, and it looks great.
*/

Comments

Posted by ray on 25 September 2010

While I agree that 'compute' is prety useless in production, I use it quite a bit in ad hoc, one-off queries. I guess I will have to learn how to use rollup...

Posted by Phil Factor on 25 September 2010

You're right about that, but it is strictly for one's own use for reports. For giving reports to managers and colleagues, I usually do them in HTML as you can then print them up nicely or convert them to Excel files. (rename them as xls, read them in to Excel  and then 'Save As') For that, I usually use CUBE or ROLLUP.

Robyn and I show how to generate HTML reports here

www.simple-talk.com/.../crosstab-pivot-table-workbench

Posted by john barnett on 25 September 2010

Slight mistake in your code.  In the second example:

HumanResourcees.Employee.EmployeeID

should be:

HumanResources.Employee.EmployeeID

Without this you get a "Multi part identifier cannot be found" error message.

Posted by Phil Factor on 25 September 2010

Thanks John. Something went wrong in the formatting stage! I've altered the source so that nobody else hits the problem.

Posted by PYDSS517 on 27 September 2010

Compute!  Wow, now I really do feel old.  LOL.  Thanks for the new and improved way.

Posted by Andy.Roberts on 28 September 2010

Thanks John. This is much easier then the Compute, which I still use sometimes.

 Initially I got a bit lost in the code to handle the Adventureworks table (which I don't have access to) and a simpler example might help like -

Create Table #names

(lastname varchar(30),

firstname varchar(30),

no_of_films int)

Insert #names

Select 'Duck', 'Daffy',  50 UNION ALL

Select 'Duck', 'Donald', 50 UNION ALL

Select 'Duck', 'Yanky Doodle', 1 UNION ALL

Select 'Marx', 'Groucho', 50 UNION ALL

Select 'Marx', 'Harpo',   30 UNION ALL

Select 'Marx', 'Chico',   30 UNION ALL

Select 'Marx', 'Zeppo',   5 UNION ALL

Select 'Marx', 'Gummo',   0

Select lastname,

firstname,

sum(no_of_films) 'Total films'

From #Names

Group By lastname, firstname

With Rollup

 You can also use the Grouping function to make the output clearer (and perhaps a bit more like Compute) -

Select lastname,

firstname,

sum(no_of_films) 'Total films'

Case When GROUPING(firstname) = 1 Then

  Case When GROUPING(lastname) = 1

Then 'Grand Total'

Else 'Total for ' + lastname

End

Else ''

End

From #Names

Group By lastname, firstname

With Rollup

 Which gives -

lastname   firstname       Total films

---------- --------------- ----------- ---------------------

Duck       Daffy           50          

Duck       Donald          50          

Duck       Yanky Doodle    1          

Duck       NULL            101         Total for Duck

Marx       Chico           30          

Marx       Groucho         50          

Marx       Gummo           0          

Marx       Harpo           30          

Marx       Zeppo           5          

Marx       NULL            115         Total for Marx

NULL       NULL            216         Grand Total

Leave a Comment

Please register or log in to leave a comment.