Exam 70-761 topic 1 question 193 discussion

Actual exam question from Microsoft's 70-761

Question #: 193
Topic #: 1

DRAG DROP -
You are a database administrator for an online retail store. You create a table to track orders by running the following Transact-SQL statement:

You need to create a report that includes the following information:
✑ Total sales for each year
✑ Total sales for each category
✑ Total sales for each category per year
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segment to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

Show Suggested Answer

Suggested Answer:

Box 1: GROUP BY -

Box 2: CUBE -
GROUP BY CUBE creates groups for all possible combinations of columns. For GROUP BY CUBE (a, b) the results has groups for unique values of (a, b), (NULL, b), (a, NULL), and (NULL, NULL).
Example: This code runs a GROUP BY CUBE operation on Country and Region.
SELECT Country, Region, SUM(Sales) AS TotalSales

FROM Sales -
GROUP BY CUBE (Country, Region);
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

by mlourinho at Nov. 21, 2019, 3:16 p.m.

Comments

Submit Cancel

mcfuw

Highly Voted 5 years, 5 months ago

With ROLL UP returns GROUP BY (category_id, YEAR(OrderDate)) GROUP BY (category_id) GROUP BY () Which means that there is no Total Sales for each year. Corect answer is GROUP BY CUBE.

upvoted 20 times

Risheesh

5 years, 5 months ago

roll up does not return all possible result So roll up is not suitable for this place

upvoted 2 times

...

Marshy

5 years, 3 months ago

Correct! DROP TABLE IF EXISTS test CREATE TABLE test( OrderId INT NOT NULL, Amount MONEY NOT NULL, CategoryID INT NOT NULL, OrderDate Datetime NOT null ) GO Insert INTO test (OrderId , Amount , CategoryID ,OrderDate ) VALUES (1, 20.5, 1, GETDATE()), (1, 20.00, 3, DATEADD(MONTH,1,GETDATE())), (1, 50.5,2,DATEADD(MONTH,2,GETDATE())), (1, 70.5,1, DATEADD(MONTH,23,GETDATE())), (1, 20.00,2, DATEADD(MONTH,4,GETDATE())), (1, 25.5,2, DATEADD(MONTH,5,GETDATE())), (1, 30.5,1, DATEADD(MONTH,12,GETDATE())) SELECT COALESCE(CONVERT(NVARCHAR(20),CategoryID),'YearTotal'), COALESCE(CONVERT(NVARCHAR(20),YEAR(OrderDate)),'CatTotal') AS Year,SUM(Amount) AS Value FROM dbo.test GROUP BY ROLLUP (CategoryID, YEAR(OrderDate) ) SELECT COALESCE(CONVERT(NVARCHAR(20),CategoryID),'YearTotal'), COALESCE(CONVERT(NVARCHAR(20),YEAR(OrderDate)),'CatTotal') AS Year,SUM(Amount) AS Value FROM dbo.test GROUP BY cube (CategoryID, YEAR(OrderDate) )

upvoted 5 times

...

damirbek369

Most Recent 5 years, 7 months ago

Why ROLLUP?

upvoted 2 times

...

mlourinho

5 years, 7 months ago

I disagree, I think the right answer is ROLLUP

upvoted 2 times

...