How to calculate grand totals with SQL Server
- SQL92 define a function of the group by attribute: WITH ROLLUP
- The WITH ROLLUP will do the grand total.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
use adventureworks; go select productid, locationid, quantity from production.productinventory where productid <=350 order by productid, locationid; go
productid locationid quantity ----------- ---------- -------- 1 1 408 1 6 324 1 50 353 2 1 427 2 6 318 2 50 364 3 1 585 3 6 443 3 50 324 4 1 512 4 6 422 4 50 388 316 5 532 316 10 388 316 50 441 317 1 283 317 5 158 317 50 152 318 1 136 318 5 171 318 50 132 319 1 308 319 5 184 319 50 305 320 1 481 320 5 372 320 50 283 321 1 569 321 5 540 321 50 641 322 1 622 322 5 587 322 50 475 323 1 603 323 5 568 323 50 513 324 1 585 324 5 568 324 50 476 325 1 569 325 6 540 325 60 641 326 1 622 326 6 587 326 60 475 327 5 408 327 10 443 327 50 513 328 5 568 328 10 585 328 50 476 329 5 558 329 10 576 329 50 467 330 5 548 330 10 566 330 50 457 331 5 574 331 10 441 331 50 390 332 5 344 332 10 233 332 50 267 341 1 372 341 50 392 341 60 339 342 1 369 342 50 388 342 60 336 343 1 568 343 50 606 343 60 499 344 1 358 344 50 332 344 60 382 345 1 321 345 50 315 345 60 300 346 1 585 346 50 476 346 60 641 347 1 318 347 50 246 347 60 332 348 1 323 348 50 251 348 60 337 349 1 328 349 50 256 349 60 342 350 5 622 350 10 406 350 50 313 (93 row(s) affected)
- I only used a small section of the data, products with an ID of less or equal to 350.
- This sample size is sufficient to demonstrate the WITH ROLLUP .
Grand Total: WITH ROLLUP
select productid, sum(quantity) as 'Ttl Qty' from production.productinventory where productid <=350 group by productid with rollup; go
productid Ttl Qty ----------- ----------- 1 1085 2 1109 3 1352 4 1322 316 1361 317 593 318 439 319 797 320 1136 321 1750 322 1684 323 1684 324 1629 325 1750 326 1684 327 1364 328 1629 329 1601 330 1571 331 1405 332 844 341 1103 342 1093 343 1673 344 1072 345 936 346 1702 347 896 348 911 349 926 350 1341 NULL 39442 (32 row(s) affected)
Please note the differences:
- SELECT productid, SUM(quantity) and GROUP BY productid does the subtotals for each product.
- GROUP BY productid WITH ROLLUP does the job of the grand total.
- No ORDER BY is needed, the order of the productid by is done by the GROUP BY.
The last line is the grand total with: NULL 39442
Grand total proper formatting
Need to replace the grand total of: NULL 39442 with Gd Total: 39442.
select coalesce(cast(productid as varchar(8)), 'Gd Total:') as 'Product',
sum(quantity) as 'Ttl Qty'
from production.productinventory
where productid <=350
group by productid with rollup;
go
Product Ttl Qty --------- ----------- 1 1085 2 1109 3 1352 4 1322 316 1361 317 593 318 439 319 797 320 1136 321 1750 322 1684 323 1684 324 1629 325 1750 326 1684 327 1364 328 1629 329 1601 330 1571 331 1405 332 844 341 1103 342 1093 343 1673 344 1072 345 936 346 1702 347 896 348 911 349 926 350 1341 Gd Total: 39442 (32 row(s) affected)
- COALESCE(CAST(productid AS VARCHAR(8)), 'Gd Total:') will place the 'Gd Total:' wording.
- If the value of productid is null, then the COALESCE will display 'Gd Total:'. The value of productid on the grand total is null. If the value of productid is not null, then it will use the value of the GROUP BY, the productid.
- The CAST(productid AS VARCHAR(8)) is needed to convert the numeric productid to a string to allow for 'Gd Total:' or you will get:
Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value 'Gd Total:' to data type int.