Database Reference
In-Depth Information
and all the preceding rows in the window. Finally, in the main query, we have
to select the countries in
CumSalesCountry
whose cumulative sales amount is
less or equal than the minimum value that is higher or equal to the 50% of
the total sales amount.
Query 6.8.
Total sales and average monthly sales by employee and year.
WITH MonthlySalesEmp AS (
SELECT E.FirstName +
''
+ E.LastName AS EmployeeName,
T.Year, T.MonthNumber,
SUM(SalesAmount) AS SalesAmount
FROM Sales S, Employee E, Time T
WHERE S.EmployeeKey = E.EmployeeKey AND
S.OrderDateKey = T.TimeKey
GROUP BY E.FirstName, E.LastName, T.Year, T.MonthNumber )
SELECT EmployeeName, Year,
FORMAT(SUM(SalesAmount),
'
$###,##0.00
'
)ASSalesAmount,
FORMAT(AVG(SalesAmount),
'
$###,##0.00
'
) AS AvgMonthlySales
FROM MonthlySalesEmp
GROUP BY EmployeeName, Year
ORDER BY EmployeeName, Year
The table defined in the
WITH
clause computes the monthly sales by
employee. In the query, we group the tuples of this table by employee and
year, and the
SUM
and
AVG
functions are applied to obtain, respectively, the
total yearly sales and the average monthly sales.
Query 6.9.
Total sales amount and total discount amount per product and
month.
SELECT P.ProductName, dbo.MonthYear(T.MonthNumber,T.Year) AS Month,
FORMAT(SUM(F.SalesAmount),
'
$###,##0.00
'
) AS SalesAmount,
FORMAT(SUM(F.UnitPrice * F.Quantity * F.Discount),
'
$###,##0.00
'
)ASTotalDisc
FROM Sales F, Time T, Product P
WHERE F.OrderDateKey = T.TimeKey AND F.ProductKey = P.ProductKey
GROUP BY P.ProductName, T.Year, T.MonthNumber
ORDER BY P.ProductName, T.Year, T.MonthNumber
Here, we group the sales by product and month. Then, the
SUM
aggregation
function is used for obtaining the total sales and the total discount amount.
Query 6.10.
Monthly year-to-date sales for each product category.
WITH SalesByCategoryMonth AS (
SELECT CategoryName, Year, MonthNumber, MonthName,
SUM(SalesAmount) AS SalesAmount
FROM SalesS,ProductP,CategoryC,TimeT
WHERE S.OrderDateKey = T.TimeKey AND
S.ProductKey = P.ProductKey AND
P.CategoryKey = C.CategoryKey
GROUP BY CategoryName, Year, MonthNumber, MonthName )
SELECT
CategoryName,
MonthName +
''
+ CAST(Year AS CHAR(4)) AS Month,
FORMAT(SUM(SalesAmount) OVER (PARTITION BY
Search WWH ::
Custom Search