My assignment asked me to find out "how many invoices are written for each date?"
I was a little stuck and asked my professor for help. She emailed me a query that would answer the question, "How many stoves of each type and version have been built?
For a challenge but no extra points, include the total number of stoves."
This was the query she sent me:
SELECT STOVE.Type + STOVE.Version AS 'Type+Version'
, COUNT(*) AS 'The Count'
GROUP BY STOVE.Type + STOVE.Version WITH ROLLUP;
, COUNT(InvoiceNbr) AS 'Number of Invoices'
GROUP BY InvoiceDt WITH ROLLUP
ORDER BY InvoiceDt ASC;
You won't see any difference since you're only rolling up a single column. Consider an example where we do
ROLLUP (YEAR, MONTH, DAY)
ROLLUP, it will have the following outputs:
YEAR, MONTH, DAY YEAR, MONTH YEAR ()
CUBE, it will have the following:
YEAR, MONTH, DAY YEAR, MONTH YEAR, DAY YEAR MONTH, DAY MONTH DAY ()
CUBE essentially contains every possible rollup scenario for each node whereas
ROLLUP will keep the hierarchy in tact (so it won't skip MONTH and show YEAR/DAY, whereas
This is why you didn't see a difference since you only had a single column you were rolling up.
Hope that helps.