CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Scott Kramer
Scott Kramer

Reputation: 1731

sql sub and grand total

Is there a nice consise way to get grand totals here-- this query shows sub totals for each day, but how can I get the grand total of each sub total without a sub query or another query

also is there something better to use than compute sum? 

SELECT loguser,logdate,logaction,logtime
FROM log WHERE loguser IN
('scott')   AND logdate  
BETWEEN '2011-06-01' AND '2011-06-15'   
ORDER BY logdate DESC    
COMPUTE SUM(logtime) BY logdate

SQL Server 2008 R2

Upvotes: 0

Views: 3739

Answers (3)

mikeY
mikeY

Reputation: 519

I think you should look at using ROLLUP over COMPUTE as Dems suggests, but if what you want is 3 things returned- 1) the daily logtime data, 2) the daily subtotal; and, 3) the grand total (total of subtotals) then I think you will need to UNION a query that finds the first two like the one you have with a query that finds the grand total.

Upvotes: 0

Ovais Khatri
Ovais Khatri

Reputation: 3211

Better way to get grand total is to use front end..instead of query.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

I'm not familiar with COMPUTE as you've used it, but this (or a variation on it) should work in most SQL dialects...

SELECT loguser,logdate,logaction,SUM(logtime)
FROM log WHERE loguser IN
('scott')   AND logdate  
BETWEEN '2011-06-01' AND '2011-06-15'
GROUP BY loguser,logdate,logaction WITH ROLLUP
ORDER BY logdate DESC

Upvotes: 3

Related Questions