Using ch10_abc_markets database, write the SQL code to select the invoice year f
ID: 3670161 • Letter: U
Question
Using ch10_abc_markets database, write the SQL code to select the invoice year from inv_date with the column name invoice Year, the invoice month from inv_date with the column name invoice Month, the invoice day from inv_date with the column name invoice Day, and the sum of the invoice total with the column name invoice Total from the invoice table. Group the results by year, month, and day using the ROLLUP sub clause. What does the roll up sub clause provide in terms of grouping and how does it differ from the CUBE sub clause?Explanation / Answer
The ROLLUP, CUBE, and GROUPING SETS operators are extensions of the GROUP BY clause. The ROLLUP, CUBE, or GROUPING SETS operators can generate the same result set as when you use UNION ALL to combine single grouping queries; however, using one of the GROUP BY operators is usually more efficient.
The GROUPING SETS operator can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator. When all the groupings that are generated by using a full ROLLUP or CUBE operator are not required, you can use GROUPING SETS to specify only the groupings that you want. The GROUPING SETS list can contain duplicate groupings; and, when GROUPING SETS is used with ROLLUP and CUBE, it might generate duplicate groupings. Duplicate groupings are retained as they would be by using UNION ALL.
ROLLUP and CUBE Compare
Queries that use the ROLLUP and CUBE operators generate some of the same result sets The CUBE operator generates a result set that can be used for cross tabulation reports.
For example, given a time dimension with the levels or attributes year, month, and day; the following ROLLUP operation generates the following groupings.
Operation
Groupings
ROLLUP (DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate))
year, month, day
year, month
year
()
Given a location dimension with the levels region and city concatenated with the time-dimension levels year, month, and day, the following ROLLUP operation outputs the following groupings.
Operation
Groupings
ROLLUP (region, city),
ROLLUP (DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate))
region, city, year, month, day
region, city, year, month
region, city, year
region, city
region, year, month, day
region, year, month
region, year
region
year, month, day
year, month
year
()
A CUBE operation of the same levels from the location and time dimensions outputs the following groupings.
Operation
Grouping
CUBE (region, city
,DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate))
region, city, year, month, day
region, city, year, month
region, city, year
region, city
region, city, month, day
region, city, month
region, city, day
region, city, year, day
region, city, day
region, year, month, day
region, year, month
region, year
region, month, day
region, month
region, year, day
region, day
region
city, year, month, day
city, year, month
city, year
city, month, day
city, month
city, year, day
city, day
year, month, day
year, month
year
year, day
month, day
month
day
()
EXAMPLES
Using GROUP BY ROLLUP
In the following example, the ROLLUP operator returns a result set that contains the following groupings:
The number of groupings that is generated by ROLLUP is the same as the number of columns in the ROLLUP list plus a grand total grouping. The number of rows in a grouping is determined by the number of unique combinations of values in the columns of the grouping.
Region
Country
Store
SalesPersonID
Total Sales
NULL
NULL
NULL
NULL
297597.8
Europe
NULL
NULL
NULL
297597.8
Europe
DE
NULL
NULL
18551.07
Europe
DE
Versatile Sporting Goods Company
NULL
18551.07
Europe
DE
Versatile Sporting Goods Company
284
859.232
Europe
DE
Versatile Sporting Goods Company
289
17691.83
Europe
FR
NULL
NULL
279046.8
Europe
FR
Spa and Exercise Outfitters
NULL
279046.8
Europe
FR
Spa and Exercise Outfitters
284
32774.36
Europe
FR
Spa and Exercise Outfitters
286
246272.4
Using GROUP BY ROLLUP with the column order reversed
In the following example, the ROLLUP operator returns a result set that contains the following groupings:
The columns in the ROLLUP list are the same as those in example B, but they are in the opposite order. Columns are rolled up from right to left; therefore, the order affects the groupings. The number of rows in the result set might vary with the column order.
Region
Country
Store
SalesPersonID
Total Sales
NULL
NULL
NULL
NULL
297597.8
NULL
NULL
NULL
284
33633.59
NULL
NULL
Spa and Exercise Outfitters
284
32774.36
NULL
FR
Spa and Exercise Outfitters
284
32774.36
Europe
FR
Spa and Exercise Outfitters
284
32774.36
NULL
NULL
Versatile Sporting Goods Company
284
859.232
NULL
DE
Versatile Sporting Goods Company
284
859.232
Europe
DE
Versatile Sporting Goods Company
284
859.232
NULL
NULL
NULL
286
246272.4
NULL
NULL
Spa and Exercise Outfitters
286
246272.4
NULL
FR
Spa and Exercise Outfitters
286
246272.4
Europe
FR
Spa and Exercise Outfitters
286
246272.4
NULL
NULL
NULL
289
17691.83
NULL
NULL
Versatile Sporting Goods Company
289
17691.83
NULL
DE
Versatile Sporting Goods Company
289
17691.83
Europe
DE
Versatile Sporting Goods Company
289
17691.83
Using GROUP BY with concatenated ROLLUP operations
In the following example, the cross product of the two ROLLUP operations is returned.
Here is the result
Region
Country
Year
Month
Total Sales
NULL
NULL
NULL
NULL
966221.9606
NULL
NULL
2006
NULL
966221.9606
NULL
NULL
2006
7
109936.0248
NULL
NULL
2006
8
296651.4808
NULL
NULL
2006
9
184477.7563
NULL
NULL
2006
10
62792.5455
NULL
NULL
2006
11
213238.0125
NULL
NULL
2006
12
99126.1407
Europe
NULL
NULL
NULL
966221.9606
Europe
NULL
2006
NULL
966221.9606
Europe
NULL
2006
7
109936.0248
Europe
NULL
2006
8
296651.4808
Europe
NULL
2006
9
184477.7563
Europe
NULL
2006
10
62792.5455
Europe
NULL
2006
11
213238.0125
Europe
NULL
2006
12
99126.1407
Europe
FR
NULL
NULL
966221.9606
Europe
FR
2006
NULL
966221.9606
Europe
FR
2006
7
109936.0248
Europe
FR
2006
8
296651.4808
Europe
FR
2006
9
184477.7563
Europe
FR
2006
10
62792.5455
Europe
FR
2006
11
213238.0125
Europe
FR
2006
12
99126.1407
Using GROUP BY CUBE
In the following example, the CUBE operator returns a result set that has one grouping for all possible combinations of columns in the CUBE list and a grand total grouping.
Here is the result
Region
Country
Store
SalesPersonID
Total Sales
NULL
NULL
NULL
NULL
254013.6014
NULL
NULL
NULL
287
28461.1854
NULL
NULL
NULL
288
17073.0655
NULL
NULL
NULL
290
208479.3505
NULL
NULL
Spa and Exercise Outfitters
NULL
236210.9015
NULL
NULL
Spa and Exercise Outfitters
287
27731.551
NULL
NULL
Spa and Exercise Outfitters
290
208479.3505
NULL
NULL
Versatile Sporting Goods Company
NULL
17802.6999
NULL
NULL
Versatile Sporting Goods Company
287
729.6344
NULL
NULL
Versatile Sporting Goods Company
288
17073.0655
NULL
DE
NULL
NULL
17802.6999
NULL
DE
NULL
287
729.6344
NULL
DE
NULL
288
17073.0655
NULL
DE
Versatile Sporting Goods Company
NULL
17802.6999
NULL
DE
Versatile Sporting Goods Company
287
729.6344
NULL
DE
Versatile Sporting Goods Company
288
17073.0655
NULL
FR
NULL
NULL
236210.9015
NULL
FR
NULL
287
27731.551
NULL
FR
NULL
290
208479.3505
NULL
FR
Spa and Exercise Outfitters
NULL
236210.9015
NULL
FR
Spa and Exercise Outfitters
287
27731.551
NULL
FR
Spa and Exercise Outfitters
290
208479.3505
Europe
NULL
NULL
NULL
254013.6014
Europe
NULL
NULL
287
28461.1854
Europe
NULL
NULL
288
17073.0655
Europe
NULL
NULL
290
208479.3505
Europe
NULL
Spa and Exercise Outfitters
NULL
236210.9015
Europe
NULL
Spa and Exercise Outfitters
287
27731.551
Europe
NULL
Spa and Exercise Outfitters
290
208479.3505
Europe
NULL
Versatile Sporting Goods Company
NULL
17802.6999
Europe
NULL
Versatile Sporting Goods Company
287
729.6344
Europe
NULL
Versatile Sporting Goods Company
288
17073.0655
Europe
DE
NULL
NULL
17802.6999
Europe
DE
NULL
287
729.6344
Europe
DE
NULL
288
17073.0655
Europe
DE
Versatile Sporting Goods Company
NULL
17802.6999
Europe
DE
Versatile Sporting Goods Company
287
729.6344
Europe
DE
Versatile Sporting Goods Company
288
17073.0655
Europe
FR
NULL
NULL
236210.9015
Europe
FR
NULL
287
27731.551
Europe
FR
NULL
290
208479.3505
Europe
FR
Spa and Exercise Outfitters
NULL
236210.9015
Europe
FR
Spa and Exercise Outfitters
287
27731.551
Europe
FR
Spa and Exercise Outfitters
290
208479.3505
Using CUBE with composite elements
In the following example, the CUBE operator returns a result set that has one grouping for all possible combinations of columns in the CUBE list and a grand total grouping.
The operator processes the grouped columns (T.[Group], T.CountryRegionCode) and (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) each as a single column.
Here is the result
Region
Country
Year
Month
Total Sales
NULL
NULL
NULL
NULL
966221.9606
NULL
NULL
2006
7
109936.0248
NULL
NULL
2006
8
296651.4808
NULL
NULL
2006
9
184477.7563
NULL
NULL
2006
10
62792.5455
NULL
NULL
2006
11
213238.0125
NULL
NULL
2006
12
99126.1407
Europe
FR
NULL
NULL
966221.9606
Europe
FR
2006
7
109936.0248
Europe
FR
2006
8
296651.4808
Europe
FR
2006
9
184477.7563
Europe
FR
2006
10
62792.5455
Europe
FR
2006
11
213238.0125
Europe
FR
2006
12
99126.1407
Operation
Groupings
ROLLUP (DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate))
year, month, day
year, month
year
()
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.