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

Advertisements

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s