Problem
- How to get both details rows and the subtotals.
- The SUM and GROUP BY with only display the subtotals without the detail rows.
- The WITH ROLLUP gives the grands totals.
Answer
- The COMPUTE allows both the detail rows and the subtotal rows.
Data used
USE adventureworks;
go
SELECT productid, locationid, quantity
FROM production.productinventory
WHERE productid <=330
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
(57 row(s) affected)
- I only used a small section of the data, products with an ID of less or equal to 330.
- This sample size is sufficient to demonstrate the COMPUTE .
Subtotals: COMPUTE
USE adventureworks;
go
SELECT productid, quantity AS 'Qty'
FROM production.productinventory
WHERE productid <=330
ORDER BY productid
COMPUTE SUM(quantity) BY productid;
productid Qty
----------- ------
1 408
1 324
1 353
sum
-----------
1085
productid Qty
----------- ------
2 427
2 318
2 364
sum
-----------
1109
productid Qty
----------- ------
3 585
3 443
3 324
sum
-----------
1352
productid Qty
----------- ------
4 512
4 422
4 388
sum
-----------
1322
productid Qty
----------- ------
316 532
316 388
316 441
sum
-----------
1361
productid Qty
----------- ------
317 283
317 158
317 152
sum
-----------
593
productid Qty
----------- ------
318 136
318 171
318 132
sum
-----------
439
productid Qty
----------- ------
319 308
319 184
319 305
sum
-----------
797
productid Qty
----------- ------
320 481
320 372
320 283
sum
-----------
1136
productid Qty
----------- ------
321 569
321 540
321 641
sum
-----------
1750
productid Qty
----------- ------
322 622
322 587
322 475
sum
-----------
1684
productid Qty
----------- ------
323 603
323 568
323 513
sum
-----------
1684
productid Qty
----------- ------
324 585
324 568
324 476
sum
-----------
1629
productid Qty
----------- ------
325 569
325 540
325 641
sum
-----------
1750
productid Qty
----------- ------
326 622
326 587
326 475
sum
-----------
1684
productid Qty
----------- ------
327 408
327 443
327 513
sum
-----------
1364
productid Qty
----------- ------
328 568
328 585
328 476
sum
-----------
1629
productid Qty
----------- ------
329 558
329 576
329 467
sum
-----------
1601
productid Qty
----------- ------
330 548
330 566
330 457
sum
-----------
1571
(76 row(s) affected)
- If you use COMPUTE with the BY, you must also use ORDER BY.
- The columns listed after by must be the same as those listed after ORDER BY, and must be in the order.
Improvements
- Lets face it, the output looks very crummy.
- You should NOT use the compute if you can.
- You should use a proper report writer to do the job such as (Crystal Reports,...).
Grand Totals with COMPUTE
SELECT productid, quantity AS 'Qty'
FROM production.productinventory
WHERE productid <=330
ORDER BY productid
COMPUTE SUM(quantity);
productid Qty
----------- ------
1 408
1 324
1 353
2 427
2 318
2 364
3 585
3 443
3 324
4 512
4 422
4 388
316 532
316 388
316 441
317 283
317 158
317 152
318 136
318 171
318 132
319 308
319 184
319 305
320 481
320 372
320 283
321 569
321 540
321 641
322 622
322 587
322 475
323 603
323 568
323 513
324 585
324 568
324 476
325 569
325 540
325 641
326 622
326 587
326 475
327 408
327 443
327 513
328 568
328 585
328 476
329 558
329 576
329 467
330 548
330 566
330 457
sum
-----------
25540
(58 row(s) affected)
