Tagi: SUM

Zadanie 24.

Wcielmy w życie zasadę Pareto (zasada 80/20). Mówi ona, że tylko ok. 20% klientów firmy odpowiada za ok. 80% jej przychodów. Sprawdźmy, czy podobna relacja zachodzi i tu.

1. Utwórz zapytanie, które zwróci nazwę firmy, łączną wartość zamówień firmy, łączną ogólną wartość zamówień.

2. Zmodyfikuj zapytanie tak, by zwracało również procentowy udział wartości zamówień danej firmy.

3. Powyższe zapytanie zmodyfikuj jeszcze raz, tak, aby pokazywało również zsumowane punkty procentowe (poczynając od najwyższych wartości).

Baza: Northwind, Tabela: Sales Totals by Amount (widok), Kolumny: CompanyName, SaleAmount, Wynik: 31 rows

s24

 

 

 

 

Rozwiązanie

1. SELECT DISTINCT CompanyName, SUM(SaleAmount) OVER(PARTITION BY CompanyName) as TotalByCust, SUM(SaleAmount) OVER() as Total
FROM dbo.[Sales Totals by Amount];

 

2. SELECT DISTINCT CompanyName, SUM(SaleAmount) OVER(PARTITION BY CompanyName) as TotalByCust, SUM(SaleAmount) OVER() as Total,
CAST(100. * SUM(SaleAmount) OVER(PARTITION BY CompanyName) / SUM(SaleAmount) OVER() as DECIMAL(5,2)) as Pct
FROM dbo.[Sales Totals by Amount];

 

3. WITH CTE AS (
SELECT DISTINCT CompanyName, SUM(SaleAmount) OVER(PARTITION BY CompanyName) as TotalByCust, SUM(SaleAmount) OVER() as Total,
CAST(100. * SUM(SaleAmount) OVER(PARTITION BY CompanyName) / SUM(SaleAmount) OVER() as DECIMAL(5,2)) as Pct
FROM dbo.[Sales Totals by Amount]
)

SELECT CompanyName, TotalByCust, Total, Pct, SUM(Pct) OVER(ORDER BY Pct DESC) as PctSum
FROM CTE
ORDER BY Pct DESC;

[collapse]

Zadanie 23.

Korzystając z widoku Sales Totals by Amount, utwórz zapytanie, które zwróci nazwę firmy, numer zamówienia, wartość zamówienia, procentowy udział zamówienia oraz łączną wartość zamówień klienta.

Baza: Northwind, Tabela: dbo.Sales Totals by Amount (widok), Kolumny: CompanyName, OrderID, SaleAmount, Wynik: 66 rows

s23

 

 

 

 

Rozwiązanie

SELECT CompanyName, OrderID, SaleAmount,
CAST(100. * SaleAmount / SUM(SaleAmount) OVER(PARTITION BY CompanyName) as DECIMAL(5,2)) as 'Percent',
SUM(SaleAmount) OVER(PARTITION BY CompanyName) as TotalByCust
FROM dbo.[Sales Totals by Amount]
ORDER BY CompanyName;

[collapse]

Zadanie 16.

Wykorzystamy tabelę wynikową uzyskaną w zadaniu 15.

1. Powyższą tabelę przekształć do postaci wspólnego wyrażenia tablicowego (CTE), nazwanego Tab.

2. W kwerendzie zewnętrznej wybierz “20% najcenniejszych klientów”: nazwę firmy, łączną wartość zamówień złożonych przez daną firmę oraz łączną wartość wszystkich zamówień złożonych przez wszystkich klientów. Interesuje nas tylko 20% firm z najwyższymi łącznymi wartościami zamówień.

Baza: Northwind, Tabele: CTE Tab, dbo.order Subtotals, Kolumny: CompanyName, Subtotal, Wynik: 18 rows

s16

 

 

 

 

Rozwiązanie

WITH Tab AS (
SELECT o.OrderID, c.CompanyName, os.Subtotal
FROM dbo.Orders as o JOIN dbo.Customers as c on o.CustomerID = c.CustomerID
JOIN dbo.[Order Subtotals] as os on o.OrderID = os.OrderID
)

SELECT TOP (20) PERCENT CompanyName, SUM(Subtotal) as 'CustSum', ( SELECT SUM(Subtotal) FROM dbo.[Order Subtotals] ) as TotalSum
FROM Tab
GROUP BY CompanyName
ORDER BY CustSum DESC;

[collapse]

Zadanie 13.

1. Utwórz zapytanie, które z tabeli Order Details zwróci numer zamówienia oraz jego łączną wartość (suma).

2. Powyższe zapytanie przekształć do postaci tabeli pochodnej; w kwerendzie zewnętrznej wybierz numer zamówienia, sumę oraz ID klienta dla zamówień, których wartość wynosi conajmniej 10 000. (Uwaga: potrzebne będzie złączenie z tabelą dbo.Orders)

Baza: Northwind, Tabele: dbo.Order Details, dbo.Order, Kolumny: OrderID, UnitPrice, Quantity, Wynik: 14 rows

s13

 

 

 

 

Rozwiązanie

SELECT Tab.OrderID, Tab.suma, Ord.CustomerID
FROM (
SELECT OrderID, SUM(UnitPrice*Quantity) as suma
FROM dbo.[Order Details]
GROUP BY OrderID) as Tab JOIN dbo.Orders as Ord on Tab.OrderID = Ord.OrderID
WHERE suma >= 10000
ORDER BY suma DESC;

[collapse]

Zadanie 19.

Wyznacz 5 klientów, którzy mają najwyższą wartość zamówień.

Baza: Northwind, Tabele: dbo.Customers, dbo.Orders, dbo.Order Details, Kolumny: CustomerID, CompanyName, Quantity, UnitPrice, Wynik: 5 rows

24

Rozwiązanie

SELECT C.CustomerID, C.CompanyName, SUM(OD.Quantity * OD.UnitPrice) as Suma FROM dbo.Customers as C

JOIN dbo.Orders as O on C.CustomerID = O.CustomerID

JOIN dbo.[Order Details] as OD on O.OrderID = OD.OrderID

GROUP BY C.CustomerID, C.CompanyName

ORDER BY Suma DESC

OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

[collapse]

Zadanie 17.

Policz wartości zamówień, zrealizowane przez każdego pracownika w 1997 roku.

Baza: Northwind, Tabele: dbo.Employees, dbo.Orders, dbo.Order Details, Kolumny: EmployeeID, LastName, Title, UnitPrice, Quantity, Wynik: 9 rows

22

Rozwiązanie

SELECT E.EmployeeID as ID, E.LastName, E.Title, SUM(OD.UnitPrice * OD.Quantity) as Sprzedaz

FROM dbo.Employees as E

JOIN dbo.Orders as O on E.EmployeeID = O.EmployeeID

JOIN dbo.[Order Details] as OD on O.OrderID = OD.OrderID

WHERE O.OrderDate >= '19970101' AND O.OrderDate < '19980101'

GROUP BY E.EmployeeID, E.LastName, E.Title

ORDER BY Sprzedaz DESC;

[collapse]

Zadanie 14.

Stwórz raport sprzedażowy – oblicz wartość sprzedaży w każdym miesiącu 1997 roku.

Baza: Northwind, Tabele: dbo.Orders, dbo.Order Details, Kolumny: OrderDate, UnitPrice, Quantity, Wynik: 12 rows

19

Rozwiązanie

SELECT YEAR(O.OrderDate) as Rok, MONTH(O.OrderDate) as Miesiac, SUM(OD.UnitPrice * OD.Quantity) as Sprzedaz

FROM dbo.Orders as O

JOIN dbo.[Order Details] as OD on O.OrderID = OD.OrderID

WHERE O.OrderDate >= '19970101' AND  O.OrderDate < '19980101'

GROUP BY YEAR(O.OrderDate), MONTH(O.OrderDate)

ORDER BY Miesiac;

[collapse]
Recent Entries »