SIMULATION
You create a table named Products.Sales by running the following Transact-SQL statement:
CREATE TABLE Products.Sales (
SalesId int IDENTIFY(1,1) PRIMARY KEY,
SalesDate DateTime NOT NULL,
SalesAmount decimal(18,2) NULL
)
You add the following data to the table.
You are developing a report to display monthly sales data.
You need to create a Transact-SQL query to meet the following requirements:
Retrieve a column for the year followed by a column for each month from January through December.
Include the total sales amount for each month.
Aggregate columns by year, month, and then amount.
Construct the query using the following guidelines:
Use the MONTH keyword as the interval when using the DATANAME function.
Do not modify the provided IN clause.
Do not surround object names with square brackets.
Do not use implicit joins.
Do not use the DATEPART function.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer
area that resolves the problem and meets the stated goals or requirements. You can add code within the code
that has been provided as well as below it.
1. SELECT * FROM
2. (SELECT YEAR(SalesData)) AS Year, DATENAME (MONTH, SalesDate) AS Month,
SalesAmount AS Amount
3.
4. ) AS MonthlySalesData
5.6. FOR Month IN (January, February, March, April, May, June, July, August,
September, October, November, December))
AS MonthNamePivot
Answer: See the explanation
Explanation:
1 SELECT * FROM
2 (SELECT YEAR(SalesData)) AS Year, DATENAME (MONTH, SalesDate) AS Month, SUM
(SalesAmount) AS Amount
3 FROM Products.Sales GROUP BY Year, Month
4 ) AS MonthlySalesData
5 PIVOT SUM(Amount)
6 FOR Month IN (January, February, March, April, May, June, July, August,
September, October, November, December))
AS MonthNamePivot
Note:
Line 2: Add SUM( ) around SalesAmount
Line 3: Add: FROM Products.Sales GROUP BY Year, Month
Line 5: Add: PIVOT SUM(Amount)
loool i mean come on group by aliases ??? so fare this dump is a joke..
0
1
SELECT * FROM
(SELECT YEAR(SalesDate) AS Year, DATENAME(MONTH, SalesDate) AS Month,
SalesAmount AS Amount
from Products.sales
) AS MonthlySalesData
PIVOT (Sum(Amount)
FOR Month IN (January, February, March, April, May, June, July, August,
September, October, November, December))
AS MonthNamePivot
15
2
Should be
SELECT * FROM
(SELECT YEAR(SalesDate)) AS Year, DATENAME (MONTH, SalesDate) AS Month, SUM(SalesAmount) AS Amount
FROM Products.Sales
GROUP BY YEAR(SalesDate), Month(SalesDate)) AS MonthlySaleData
PIVOT (SUM(Amount) FOR Month IN (January, February, March, April, May, June, July, August,
September, October, November, December)) AS MonthNamePivot
2
4
There is one bracket to much after YEAR(SalesDate) in the subselect.
Statement should be:
SELECT * FROM
(SELECT YEAR(SalesDate) AS Year, DATENAME(MONTH,SalesDate) AS Month, SalesAmount AS Amount
FROM dbo.Sales
) AS MonthlySalesData
PIVOT (SUM(Amount)
FOR Month IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS MonthNamePivot
15
0
Sorry, wrong schema. Should be:
SELECT * FROM
(SELECT YEAR(SalesDate) AS Year, DATENAME(MONTH,SalesDate) AS Month, SalesAmount AS Amount
FROM Products.Sales
) AS MonthlySalesData
PIVOT (SUM(Amount)
FOR Month IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS MonthNamePivot
22
0
agree
3
0
SUM(SalesAmount) is missing from the select clause:
SELECT * FROM
(SELECT YEAR(SalesDate) AS Year, DATENAME(MONTH,SalesDate) AS Month, SUM(SalesAmount) AS Amount
0
1
Sorry, I was wrong…
1
0
Here is the correct answer :
SELECT * FROM
(SELECT YEAR(SalesDate) as Year, DATENAME (MONTH, SalesDate) as Month,
sum (SalesAmount) AS Amount FROM Products.Sales ) AS MonthlySalesData
PIVOT (sum(Amount) FOR Month IN (January, February, March, April, May, June, July, August,
September, October, November, December))
AS MonthNamePivot
go
1
15