Using ch10_abc_markets database, write the SQL code to select the invoice year f

Question

Using ch10_abc_markets database, write the SQL code to select the invoice year frominv_date with the column name invoice Year, the invoice month from inv_date with the column name invoiceMonth, the invoice day from inv_date with the column name invoiceDay, and the sum of the invoice total with 2.)the column name invoiceTotal from the invoice table. Group the results by year, month, and day using the ROLLUP subclause. QUESTION: What does the rollup subclause provide in terms of grouping and how does it differ from the CUBE subclause?

Explanation / Answer

SELECT EXTRACT(YEAR FROM inv_date) AS invoiceYear,
EXTRACT(MONTH FROM inv_date) AS invoiceMonth,
EXTRACT(DAY FROM inv_date) AS invoiceDay,
SUM(INV_TOTAL) invoiceTotal
FROM INVOICE
GROUP BY ROLLUP (invoiceYear, invoiceMonth, invoiceDay)
ORDER BY invoiceYear, invoiceMonth, invoiceDay;

ROLLUP - Group by produces aggregation result only, but ROLLUP produces group subtotals from right to left and a grand total also.
--------------------------------
SELECT column1,
column2,
SUM(some_value) AS sum_value
FROM table
GROUP BY ROLLUP (column1, column2)
ORDER BY column1, column2;

column1 column2 sum_value
----------------------------------
1 1 4363.55
1 2 4794.76
1 3 4718.25
1 4 5387.45
1 5 5027.34
1 24291.35
2 1 5652.84
2 2 4583.02
2 3 5555.77
2 4 5936.67
2 5 4508.74
2 26237.04
50528.39

CUBE - CUBE extension generates subtotals for all combinations of the columns specifiedas described in following example.
---------------------------------
SELECT column1,
column2,
SUM(some_value) AS sum_value
FROM table
GROUP BY CUBE (column1, column2)
ORDER BY column1, column2;

column1         column2   sum_value
---------- ---------- -----------
1 1 4363.55
1 2 4794.76
1 3 4718.25
1 4 5387.45
1 5 5027.34
1 24291.35 ( this sum is specific to column1 and group made for value 1)
2 1 5652.84
2 2 4583.02
2 3 5555.77
2 4 5936.67
2 5 4508.74
2 26237.04 ( this sum is specific to column1 and group made for value 2)
1 10016.39 ( this sum is specific to column2 and group made for value 1)
2 9377.78 ( this sum is specific to column2 and group made for value 2)
3 10274.02 ( this sum is specific to column2 and group made for value 3)
4 11324.12 ( this sum is specific to column2 and group made for value 4)
5 9536.08 ( this sum is specific to column2 and group made for value 5)
50528.39 ( this is agreegate sum , specific to column1)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.