SQL GROUP BY örnekleri

Materilized View

CREATE MATERIALIZED VIEW sales_mv AS
SELECT
channel_desc channel,
prod_category category,
prod_subcategory subcategory,
prod_name product,
calendar_year year,
calendar_quarter_number quarter,
calendar_month_number month,
SUM(amount_sold) sales
FROM sales, times, products, channels
WHERE sales.time_id = times.time_id
AND sales.prod_id = products.prod_id
AND sales.channel_id = channels.channel_id
AND prod_category IN ('Photo', 'Hardware')
AND calendar_year IN (2000, 2001)
AND channel_desc IN ('Direct Sales', 'Internet')
GROUP BY channel_desc, prod_category, prod_subcategory, prod_name, calendar_year,
calendar_quarter_number, calendar_month_number;

ROLLUP EXTENSION

SQL> select year, quarter, month, sum(sales) sales
2 from sales_mv
3 where quarter in (1,2)
4 group by
5 rollup(year, quarter, month)

YEAR QUARTER MONTH SALES
----- ---------- ---------- ----------
2000 1 1 535721.14
2000 1 2 590694.56
2000 1 3 521087.59
2000 1 1647503.29
2000 2 4 441138.68
2000 2 5 494655.15
2000 2 6 435226.29
2000 2 1371020.12
2000 3018523.41
2001 1 1 571462.03
2001 1 2 568428.13
2001 1 3 763963.5
2001 1 1903853.66
2001 2 4 764267.14
2001 2 5 692348.86
2001 2 6 753519.27
2001 2 2210135.27
2001 4113988.93
7132512.34


CUBE EXTENSION

SQL> select channel, category, year, sum(sales) sales
2 from sales_mv
3 group by cube(channel, category, year)
4 order by channel, category, year
5 /
CHANNEL CATEGORY YEAR SALES
------------ ---------- ----- ----------
Direct Sales Hardware 2000 2762505.5
Direct Sales Hardware 2001 2932993.6
Direct Sales Hardware 5695499.1
Direct Sales Photo 2000 2538263.21
Direct Sales Photo 2001 2874063.97
Direct Sales Photo 5412327.18
Direct Sales 2000 5300768.71
Direct Sales 2001 5807057.57
Direct Sales 11107826.3
Internet Hardware 2000 133691.03
Internet Hardware 2001 1142198.46
Internet Hardware 1275889.49
Internet Photo 2000 441508.29



GROUPING FUNCTIONS

SQL> select year, quarter, month, sum(sales) sales,
2 grouping(year) yr, grouping(quarter) qtr, grouping(month) mth
3 from sales_mv
4 where quarter in (1,2)
5 group by
6 rollup(year, quarter, month)
7 /
YEAR QUARTER MONTH SALES YR QTR MTH
----- ---------- ---------- ---------- ---------- ---------- ----------
2000 1 1 535721.14 0 0 0
2000 1 2 590694.56 0 0 0
2000 1 3 521087.59 0 0 0
2000 1 1647503.29 0 0 1
2000 2 4 441138.68 0 0 0
2000 2 5 494655.15 0 0 0
2000 2 6 435226.29 0 0 0
2000 2 1371020.12 0 0 1
2000 3018523.41 0 1 1
2001 1 1 571462.03 0 0 0
2001 1 2 568428.13 0 0 0
2001 1 3 763963.5 0 0 0
2001 1 1903853.66 0 0 1
2001 2 4 764267.14 0 0 0
2001 2 5 692348.86 0 0 0
2001 2 6 753519.27 0 0 0
2001 2 2210135.27 0 0 1
2001 4113988.93 0 1 1
7132512.34


GROUPING_ID
SQL> select channel, category, year, sum(sales) sales,
2 grouping_id(channel,category,year) grouping_id
3 from sales_mv
4 group by cube(channel, category, year)
5 order by channel, category, year
6 /
CHANNEL CATEGORY YEAR SALES GROUPING_ID


COMPOSITE COLUMNS
SQL> select year, quarter, month, sum(sales) sales
2 from sales_mv
3 where quarter in (1,2)
4 group by
5 rollup(year, (quarter, month))
6 /
YEAR QUARTER MONTH SALES
----- ---------- ---------- ----------
2000 1 1 535721.14
2000 1 2 590694.56
2000 1 3 521087.59
2000 2 4 441138.68
2000 2 5 494655.15
2000 2 6 435226.29
2000 3018523.41
2001 1 1 571462.03


GROUPING SETS EXPRESSION
SQL> select channel, category, year, sum(sales) sales
2 from sales_mv
3 group by grouping sets (channel, category, year)
4 order by channel, category, year
5 /
CHANNEL CATEGORY YEAR SALES
------------ ---------- ----- ----------
Direct Sales 11107826.3
Internet 3378109.2
Hardware 6971388.59
Photo 7514546.89
2000 5875968.03


CONCATENATED GROUPINGS
SQL> select channel, category, year, quarter, sum(sales) sales
2 from sales_mv
3 where quarter in (1,2)
4 group by grouping sets (channel, category), grouping sets (year, quarter)
5 order by channel, category, year, quarter
6 /
CHANNEL CATEGORY YEAR QUARTER SALES
------------ ---------- ----- ---------- ----------
Direct Sales 2000 2906732.02
Direct Sales 2001 2806723.79
Direct Sales 1 2900331.1
Direct Sales 2 2813124.71
Internet 2000 111791.39