Quote:
|
Originally Posted by Marcus_A
Check out the SQL Reference Vol 1.
|
More specifically, see pages 591-596 of the DB2 9.5 for LUW version of this manual, and the examples on pages 605-611.
You can GROUP BY:
- grouping sets
- rollup
- cube
or any combination of these (also combined with single column grouping, or grouping by expression).
Example (from the manual):
Code:
SELECT MONTH(SALES_DATE) AS MONTH,
REGION,
SUM(SALES) AS UNITS_SOLD,
MAX(SALES) AS BEST_SALE,
AVG(SALES) AS AVG_UNITS_SOLD
FROM SALES
GROUP BY CUBE(MONTH(SALES_DATE),REGION)
ORDER BY MONTH, REGION
will return summaries per month, per region, per month/region combination, and a grand total row:
Code:
MONTH REGION UNITS_SOLD BEST_SALE AVG_UNITS_SOLD
----- -------------- ----------- ----------- --------------
3 Manitoba 22 7 3.14
3 Ontario-North 8 3 2.67
3 Ontario-South 34 14 4.25
3 Quebec 40 18 5.00
3 - 104 18 4.00
4 Manitoba 17 9 5.67
4 Ontario-North 1 1 1.00
4 Ontario-South 14 8 4.67
4 Quebec 11 8 5.50
4 - 43 9 4.78
12 Manitoba 2 2 2.00
12 Ontario-South 4 3 2.00
12 Quebec 2 1 1.00
12 - 8 3 1.60
- Manitoba 41 9 3.73
- Ontario-North 9 3 2.25
- Ontario-South 52 14 4.00
- Quebec 53 18 4.42
- - 155 18 3.87
dBforums
>
olap
12-28-08, 19:29
Linear Mode
