sql-aggregate functions
Using Rollup, Cube, Grouping and Grouping Sets.
Rollup – operation: It is a sub-class of group by-clause, aggregating the select statement’s output. For our test, the promotions – table in sh schema is used,
SELECT P.PROMO_CATEGORY,
P.PROMO_END_DATE,
P.PROMO_COST
FROM PROMOTIONS P
ORDER BY P.PROMO_CATEGORY;
PROMO_CATEGORY PROMO_END_DATE PROMO_COST
NO PROMOTION 99-01-01 0
TV 99-04-09 61300
TV 99-01-30 61600
TV 98-08-03 62200
TV 01-01-23 62300
TV 98-09-17 62600
TV 98-09-26 63900
TV 99-01-22 65900
SELECT DISTINCT (extract (YEAR FROM P.PROMO_END_DATE)) distinct_y
FROM PROMOTIONS P;
DISTINCT_Y
9999
2001
1999
1998
2000
SELECT DISTINCT (P.PROMO_CATEGORY) distinct_p
FROM PROMOTIONS P;
DISTINCT_P
ad news
internet
magazine
flyer
NO PROMOTION
newspaper
post
TV
radio
9 lignes sélectionnées
Total coast for each set of rows grouped by category promotion and ending year promotion.
SELECT (EXTRACT (YEAR FROM P.PROMO_END_DATE)) distinct_y,
P.PROMO_CATEGORY,
SUM(P.PROMO_COST) total_coat
FROM PROMOTIONS P
GROUP BY EXTRACT (YEAR FROM P.PROMO_END_DATE),P.PROMO_CATEGORY;
DISTINCT_Y PROMO_CATEGORY TOTAL_COAT
1999 post 1236200
1999 newspaper 1633200
2001 internet 111700
1999 flyer 720200
2000 flyer 697400
2001 magazine 381600
2001 newspaper 293400
2001 radio 176200
1998 flyer 606800
1999 magazine 1416100
9999 NO PROMOTION 0
2000 TV 1873200
1998 internet 1157100
1999 TV 2020000
1998 post 725200
1999 internet 1483600
2000 magazine 1247000
2000 newspaper 2107400
2000 post 919600
2000 internet 1503800
2001 post 432100
1998 TV 1078800
1998 radio 421700
1999 radio 207600
1999 ad news 0
1998 newspaper 1646500
2001 TV 348000
1998 magazine 358000
2000 radio 289500
2001 flyer 139000
30 lignes sélectionnées
Rollup: it generates a subtotal value for each grouped set of rows and one total value.
it starts generating aggregate values for columns and expressions specified in the group by-clause. The subtotal values are created gradualy for higher levels in one direction, from right to left.ROLLUPcreates subtotals at n+1 levels if n is the number of specified columns and expressions in the group by clause.
SELECT (EXTRACT (YEAR FROM P.PROMO_END_DATE)) distinct_y,
P.PROMO_CATEGORY,
SUM(P.PROMO_COST) total_coat
FROM PROMOTIONS P
GROUP BY ROLLUP(EXTRACT (YEAR FROM P.PROMO_END_DATE),P.PROMO_CATEGORY);
DISTINCT_Y PROMO_CATEGORY TOTAL_COAT
2000 TV 1873200
2000 post 919600
2000 flyer 697400
2000 radio 289500
2000 internet 1503800
2000 magazine 1247000
2000 newspaper 2107400
2000 8637900
1998 TV 1078800
1998 post 725200
1998 flyer 606800
1998 radio 421700
1998 internet 1157100
1998 magazine 358000
1998 newspaper 1646500
1998 5994100
1999 TV 2020000
1999 post 1236200
1999 flyer 720200
1999 radio 207600
1999 ad news 0
1999 internet 1483600
1999 magazine 1416100
1999 newspaper 1633200
1999 8716900
2001 TV 348000
2001 post 432100
2001 flyer 139000
2001 radio 176200
2001 internet 111700
2001 magazine 381600
2001 newspaper 293400
2001 1882000
9999 NO PROMOTION 0
9999 0
25230900
36 lignes sélectionnées
The rollup operation processes the standard output for the specified columns and expressions in the goup by-ckause by adding the following:
One subtotal value for each grouped set of rows.
SELECT t.distinct_y,
SUM(total_coat) total_per_year
FROM (SELECT (EXTRACT (YEAR FROM P.PROMO_END_DATE)) distinct_y,
P.PROMO_CATEGORY,
SUM(P.PROMO_COST) total_coat
FROM PROMOTIONS P
GROUP BY EXTRACT (YEAR FROM P.PROMO_END_DATE), P.PROMO_CATEGORY
)t
GROUP BY t.distinct_y
DISTINCT_Y TOTAL_PER_YEAR
1999 8716900
2001 1882000
9999 0
1998 5994100
2000 8637900
One grand total value for the output select statement
SELECT SUM (total_per_year) grand_total
FROM (SELECT t.distinct_y,
SUM(total_coat) total_per_year
FROM (SELECT (EXTRACT (YEAR FROM P.PROMO_END_DATE)) distinct_y,
P.PROMO_CATEGORY,
SUM(P.PROMO_COST) total_coat
FROM PROMOTIONS P
GROUP BY EXTRACT (YEAR FROM P.PROMO_END_DATE),P.PROMO_CATEGORY
)t
GROUP BY t.distinct_y
);
GRAND_TOTAL
25230900
Cube- operation: Generates subtotals for every possible grouping within the columns and expressions specified in the group by – clause.
CUBEtakes a specified set of grouping columns and creates subtotals for all of their possible combinations.
SELECT (EXTRACT (YEAR FROM P.PROMO_END_DATE)) distinct_y,
P.PROMO_CATEGORY,
SUM(P.PROMO_COST) total_coat
FROM PROMOTIONS P
GROUP BY CUBE(EXTRACT (YEAR FROM P.PROMO_END_DATE),P.PROMO_CATEGORY);
Statement’s output:
- Total coast
- Total coast for each promotion category
- Total coast by year
- Total cost for each promotion category by year
DISTINCT_Y PROMO_CATEGORY TOTAL_COAT
25230900
TV 5320000
post 3313100
flyer 2163400
radio 1095000
ad news 0
internet 4256200
magazine 3402700
newspaper 5680500
NO PROMOTION 0
2000 8637900
2000 TV 1873200
2000 post 919600
2000 flyer 697400
2000 radio 289500
2000 internet 1503800
2000 magazine 1247000
2000 newspaper 2107400
1998 5994100
1998 TV 1078800
1998 post 725200
1998 flyer 606800
1998 radio 421700
1998 internet 1157100
1998 magazine 358000
1998 newspaper 1646500
1999 8716900
1999 TV 2020000
1999 post 1236200
1999 flyer 720200
1999 radio 207600
1999 ad news 0
1999 internet 1483600
1999 magazine 1416100
1999 newspaper 1633200
2001 1882000
2001 TV 348000
2001 post 432100
2001 flyer 139000
2001 radio 176200
2001 internet 111700
2001 magazine 381600
2001 newspaper 293400
9999 0
9999 NO PROMOTION 0
45 lignes sélectionnées
Grouping – function: The grouping function is used to identify the rows created by cube and rollup operations. It identifies superaggregate and aggregate rows.
SELECT GROUPING(EXTRACT (YEAR FROM P.PROMO_END_DATE)) GRP_DATE,
GROUPING(P.PROMO_CATEGORY) GRP_PROMO,
(EXTRACT (YEAR FROM P.PROMO_END_DATE)) END_DATE,
P.PROMO_CATEGORY CATEGORY,
SUM(P.PROMO_COST) TOTAL_COST
FROM PROMOTIONS P
GROUP BY ROLLUP(EXTRACT (YEAR FROM P.PROMO_END_DATE),P.PROMO_CATEGORY)
ORDER BY EXTRACT (YEAR FROM P.PROMO_END_DATE),P.PROMO_CATEGORY;
GRP_DATE GRP_PROMO END_DATE CATEGORY TOTAL_COST
0 0 1998 TV 1078800
0 0 1998 flyer 606800
0 0 1998 internet 1157100
0 0 1998 magazine 358000
0 0 1998 newspaper 1646500
0 0 1998 post 725200
0 0 1998 radio 421700
0 1 1998 5994100
0 0 1999 TV 2020000
0 0 1999 ad news 0
0 0 1999 flyer 720200
0 0 1999 internet 1483600
0 0 1999 magazine 1416100
0 0 1999 newspaper 1633200
0 0 1999 post 1236200
0 0 1999 radio 207600
0 1 1999 8716900
0 0 2000 TV 1873200
0 0 2000 flyer 697400
0 0 2000 internet 1503800
0 0 2000 magazine 1247000
0 0 2000 newspaper 2107400
0 0 2000 post 919600
0 0 2000 radio 289500
0 1 2000 8637900
0 0 2001 TV 348000
0 0 2001 flyer 139000
0 0 2001 internet 111700
0 0 2001 magazine 381600
0 0 2001 newspaper 293400
0 0 2001 post 432100
0 0 2001 radio 176200
0 1 2001 1882000
0 0 9999 NO PROMOTION 0
0 1 9999 0
1 1 25230900
36 lignes sélectionnées
SELECT GROUPING(EXTRACT (YEAR FROM P.PROMO_END_DATE)) GRP_DATE,
GROUPING(P.PROMO_CATEGORY) GRP_PROMO,
NVL (
DECODE (GROUPING(P.PROMO_CATEGORY),1,
UPPER(P.PROMO_CATEGORY),INITCAP(P.PROMO_CATEGORY)
)
,’GRAND TOTAL’
) PROMO_CATEGORY_FORMATTED,
(EXTRACT (YEAR FROM P.PROMO_END_DATE)) END_DATE,
SUM(P.PROMO_COST) TOTAL_COST
FROM PROMOTIONS P
GROUP BY ROLLUP(EXTRACT (YEAR FROM P.PROMO_END_DATE), P.PROMO_CATEGORY);
GRP_DATE GRP_PROMO PROMO_CATEGORY_FORMATTED END_DATE TOTAL_COST
0 0 Tv 2000 1873200
0 0 Post 2000 919600
0 0 Flyer 2000 697400
0 0 Radio 2000 289500
0 0 Internet 2000 1503800
0 0 Magazine 2000 1247000
0 0 Newspaper 2000 2107400
0 1 GRAND TOTAL 2000 8637900
0 0 Tv 1998 1078800
0 0 Post 1998 725200
0 0 Flyer 1998 606800
0 0 Radio 1998 421700
0 0 Internet 1998 1157100
0 0 Magazine 1998 358000
0 0 Newspaper 1998 1646500
0 1 GRAND TOTAL 1998 5994100
0 0 Tv 1999 2020000
0 0 Post 1999 1236200
0 0 Flyer 1999 720200
0 0 Radio 1999 207600
0 0 Ad News 1999 0
0 0 Internet 1999 1483600
0 0 Magazine 1999 1416100
0 0 Newspaper 1999 1633200
0 1 GRAND TOTAL 1999 8716900
0 0 Tv 2001 348000
0 0 Post 2001 432100
0 0 Flyer 2001 139000
0 0 Radio 2001 176200
0 0 Internet 2001 111700
0 0 Magazine 2001 381600
0 0 Newspaper 2001 293400
0 1 GRAND TOTAL 2001 1882000
0 0 No Promotion 9999 0
0 1 GRAND TOTAL 9999 0
1 1 GRAND TOTAL 25230900
36 lignes sélectionnées
GROUPING SETS Expression
Grouping sets – expression allows you to specify which groups you want to create within group by clause.
SELECT (EXTRACT (YEAR FROM P.PROMO_END_DATE)) END_DATE,
P.PROMO_CATEGORY CATEGORY,
P.PROMO_SUBCATEGORY SUBCATEGORY,
COUNT(*) TOTAL
FROM PROMOTIONS P
WHERE EXTRACT (YEAR FROM P.PROMO_END_DATE)=1999
GROUP BY GROUPING SETS (EXTRACT (YEAR FROM P.PROMO_END_DATE),P.PROMO_CATEGORY,P.PROMO_SUBCATEGORY);
END_DATE CATEGORY SUBCATEGORY TOTAL
ad news 11
online discount 16
downtown billboard 14
freeway billboard 7
manufacture rebate news 5
radio commercial 1
manufacture rebate magazine 6
household flyer 1
hospital flyer 10
promotion movie 6
coupon news 19
TV program sponsorship 25
coupon magazine 14
ad magazine 3
TV commercial 10
newspaper 2
loyal customer discount 5
radio program sponsorship 3
billboard 1
general flyer 4
ad 6
ad news 2
internet 27
flyer 15
magazine 23
newspaper 35
post 22
TV 41
radio 4
1999 169
30 lignes sélectionnées
Note: The select will return the same result as UNION ALL GROUP BY (EXTRACT (YEAR FROM P.PROMO_END_DATE), UNION ALL GROUP BY P.PROMO_CATEGORY UNION ALL GROUP BY P.PROMO_SUBCATEGORY);
SELECT (EXTRACT (YEAR FROM P.PROMO_END_DATE)) END_DATE,
TO_CHAR(NULL) CATEGORY,
TO_CHAR(NULL) SUBCATEGORY,
COUNT(*) TOTAL
FROM PROMOTIONS P
WHERE EXTRACT (YEAR FROM P.PROMO_END_DATE)=1999
GROUP BY EXTRACT (YEAR FROM P.PROMO_END_DATE)
UNION ALL
SELECT TO_NUMBER(NULL) END_DATE,
P.PROMO_CATEGORY CATEGORY,
TO_CHAR(NULL) SUBCATEGORY,
COUNT(*) TOTAL
FROM PROMOTIONS P
WHERE EXTRACT (YEAR FROM P.PROMO_END_DATE)=1999
GROUP BY P.PROMO_CATEGORY
UNION ALL
SELECT TO_NUMBER(NULL) END_DATE,
TO_CHAR(NULL) CATEGORY,
P.PROMO_SUBCATEGORY SUBCATEGORY,
COUNT(*) TOTAL
FROM PROMOTIONS P
WHERE EXTRACT (YEAR FROM P.PROMO_END_DATE)=1999
GROUP BY P.PROMO_SUBCATEGORY
ORDER BY END_DATE DESC, CATEGORY DESC, SUBCATEGORY DESC
Let’s verify (Note)
SELECT (EXTRACT (YEAR FROM P.PROMO_END_DATE)) END_DATE,
P.PROMO_CATEGORY CATEGORY,
P.PROMO_SUBCATEGORY SUBCATEGORY,
COUNT(*) TOTAL
FROM PROMOTIONS P
WHERE EXTRACT (YEAR FROM P.PROMO_END_DATE)=1999
GROUP BY GROUPING SETS (EXTRACT (YEAR FROM P.PROMO_END_DATE),P.PROMO_CATEGORY,P.PROMO_SUBCATEGORY)
MINUS
(
SELECT (EXTRACT (YEAR FROM P.PROMO_END_DATE)) END_DATE,
TO_CHAR(NULL) CATEGORY,
TO_CHAR(NULL) SUBCATEGORY,
COUNT(*) TOTAL
FROM PROMOTIONS P
WHERE EXTRACT (YEAR FROM P.PROMO_END_DATE)=1999
GROUP BY EXTRACT (YEAR FROM P.PROMO_END_DATE)
UNION ALL
SELECT TO_NUMBER(NULL) END_DATE,
P.PROMO_CATEGORY CATEGORY,
TO_CHAR(NULL) SUBCATEGORY,
COUNT(*) TOTAL
FROM PROMOTIONS P
WHERE EXTRACT (YEAR FROM P.PROMO_END_DATE)=1999
GROUP BY P.PROMO_CATEGORY
UNION ALL
SELECT TO_NUMBER(NULL) END_DATE,
TO_CHAR(NULL) CATEGORY,
P.PROMO_SUBCATEGORY SUBCATEGORY,
COUNT(*) TOTAL
FROM PROMOTIONS P
WHERE EXTRACT (YEAR FROM P.PROMO_END_DATE)=1999
GROUP BY P.PROMO_SUBCATEGORY
);
aucune ligne sélectionnée
Oracle forms11g batch – conversion Change the screen resolution with ubuntu15.04 on windows10 hyper-v machine
About Me
Senior oracle DBA/Developer | 9X OCI Certified | OCP | OCE Sql Certified Expert
