Tellimuste arv aasta ja kuude lõikes - ROLLUP, CUBE

SELECT  ISNULL(CAST(Year(Orderdate) as Varchar), 'Kokku') as Aasta, 
        IsNULL(CAST(MONTH(OrderDate) as varchar), 'Kokku') as Kuu, COUNT(OrderID) as TellimusteArv
FROM Orders
GROUP BY Year(Orderdate) , MONTH(OrderDate) WITH ROllUP

(27 rows affected)

Total execution time: 00:00:00.012

Aasta	Kuu	TellimusteArv
1996	7	22
1996	8	25
1996	9	23
1996	10	26
1996	11	25
1996	12	31
1996	Kokku	152
1997	1	33
1997	2	29
1997	3	30
1997	4	31
1997	5	32
1997	6	30
1997	7	33
1997	8	33
1997	9	37
1997	10	38
1997	11	34
1997	12	48
1997	Kokku	408
1998	1	55
1998	2	54
1998	3	73
1998	4	74
1998	5	14
1998	Kokku	270
Kokku	Kokku	830

--Tellimuste arv aasta lõikes
SELECT 'Tellimuste Arv' as 'Years', [1996],[1997],[1998] -- tabeli päis
FROM (SELECT OrderID, year(Orderdate) as OrderYears
        FROM Orders) as Andmed --Andmed Pivot koostamiseks
PIVOT
    (COUNT(OrderID) for OrderYears IN ([1996],[1997],[1998])) as Tabel --PIVOT funktsioon

(1 row affected)

Total execution time: 00:00:00.026

Years	1996	1997	1998
Tellimuste Arv	152	408	270

--Klientide arv
SELECT OrderMonths as 'Kuu' ,[1996],[1997], [1998]-- tabeli päis
FROM (SELECT CustomerID, year(OrderDate) as OrderYears, month(orderdate) as OrderMonths
        FROM Orders) as Andmed -- andmed Pivot koostamiseks
PIVOT
    (COUNT(CustomerID) for OrderYears IN ([1996],[1997], [1998])) as Tabel

(12 rows affected)

Total execution time: 00:00:00.013

Kuu	1996	1997	1998
1	0	33	55
2	0	29	54
3	0	30	73
4	0	31	74
5	0	32	14
6	0	30	0
7	22	33	0
8	25	33	0
9	23	37	0
10	26	38	0
11	25	34	0
12	31	48	0

--Tellimuste arv ShipCountries ja aasta lõikes
SELECT Riik ,[1996],[1997], [1998]-- tabeli päis
FROM (SELECT Shipcountry as Riik ,Orderid, year(OrderDate) as OrderYears
        FROM Orders
        ) as Andmed -- andmed Pivot koostamiseks
PIVOT
    (COUNT(OrderID) for OrderYears IN ([1996],[1997], [1998])) as Tabel

(21 rows affected)

Total execution time: 00:00:00.011

Riik	1996	1997	1998
Finland	4	13	5
USA	23	60	39
Italy	3	15	10
Brazil	13	42	28
Germany	24	64	34
Switzerland	3	8	7
Mexico	9	12	7
Sweden	6	17	14
Argentina	0	6	10
Austria	8	21	11
UK	10	30	16
Poland	1	2	4
Canada	4	17	9
Ireland	5	10	4
Norway	1	2	3
France	15	39	23
Belgium	2	7	10
Spain	6	5	12
Venezuela	8	20	18
Denmark	3	11	4
Portugal	4	7	2

--Klientide arv aasta lõikes
SELECT LastName,FirstName,Empl ,[1996],[1997], [1998]-- tabeli päis
FROM (SELECT E.EmployeeID as Empl,LastName,FirstName ,Orderid, year(OrderDate) as OrderYears
        FROM Orders Inner JOIn Employees E ON E.EmployeeID =Orders.EmployeeID
        ) as Andmed -- andmed Pivot koostamiseks
PIVOT
    (COUNT(OrderID) for OrderYears IN ([1996],[1997], [1998])) as Tabel

(9 rows affected)

Total execution time: 00:00:00.016

LastName	FirstName	Empl	1996	1997	1998
Dodsworth	Anne	9	5	19	19
Leverling	Janet	3	18	71	38
Suyama	Michael	6	15	33	19
King	Robert	7	11	36	25
Davolio	Nancy	1	26	55	42
Peacock	Margaret	4	31	81	44
Buchanan	Steven	5	11	18	13
Fuller	Andrew	2	16	41	39
Callahan	Laura	8	19	54	31

--Klientide arv aasta ja kuu lõikes - PIVOT
SELECT LastName,FirstName,Empl ,OrderYears, [1] 'Jaan',[2]'Veebr',[3]'Marts',[4]'Aprill',[5]'Mai',[6]'Juuni',[7]'Juuli', [8] 'August',[9] as 'September',[10] as 'Oktoober',[11] 'November',[12] 'Detsember'-- tabeli päis
FROM (SELECT E.EmployeeID as Empl,LastName,FirstName ,Orderid, year(OrderDate) as OrderYears, MONTH(OrderDate) as Kuud
        FROM Orders  JOIn Employees E ON E.EmployeeID =Orders.EmployeeID
        ) as Andmed -- andmed Pivot koostamiseks
PIVOT
    (COUNT(OrderID) for Kuud IN ([1],[2],[3],[4],[5],[6],[7], [8] ,[9]  ,[10]  ,[11] ,[12] )) as Tabel

(27 rows affected)

Total execution time: 00:00:00.019

LastName	FirstName	Empl	OrderYears	Jaan	Veebr	Marts	Aprill	Mai	Juuni	Juuli	August	September	Oktoober	November	Detsember
Davolio	Nancy	1	1996	0	0	0	0	0	0	1	5	5	2	4	9
Davolio	Nancy	1	1997	3	2	5	1	5	4	7	3	8	7	3	7
Davolio	Nancy	1	1998	9	9	11	8	5	0	0	0	0	0	0	0
Fuller	Andrew	2	1996	0	0	0	0	0	0	1	2	5	2	2	4
Fuller	Andrew	2	1997	4	1	4	3	3	4	3	1	7	1	5	5
Fuller	Andrew	2	1998	7	3	9	18	2	0	0	0	0	0	0	0
Leverling	Janet	3	1996	0	0	0	0	0	0	4	2	1	3	4	4
Leverling	Janet	3	1997	7	9	3	5	5	6	2	4	4	7	8	11
Leverling	Janet	3	1998	10	6	12	10	0	0	0	0	0	0	0	0
Peacock	Margaret	4	1996	0	0	0	0	0	0	7	5	3	8	5	3
Peacock	Margaret	4	1997	8	6	4	8	5	5	6	11	5	7	6	10
Peacock	Margaret	4	1998	6	14	12	10	2	0	0	0	0	0	0	0
Buchanan	Steven	5	1996	0	0	0	0	0	0	3	0	1	2	2	3
Buchanan	Steven	5	1997	0	0	3	0	2	2	1	3	2	3	1	1
Buchanan	Steven	5	1998	4	6	2	1	0	0	0	0	0	0	0	0
Suyama	Michael	6	1996	0	0	0	0	0	0	2	4	3	0	3	3
Suyama	Michael	6	1997	2	2	2	4	2	2	2	2	1	4	5	5
Suyama	Michael	6	1998	3	4	7	5	0	0	0	0	0	0	0	0
King	Robert	7	1996	0	0	0	0	0	0	0	1	2	5	3	0
King	Robert	7	1997	3	1	2	6	5	1	5	3	5	1	1	3
King	Robert	7	1998	4	6	4	9	2	0	0	0	0	0	0	0
Callahan	Laura	8	1996	0	0	0	0	0	0	2	6	3	2	2	4
Callahan	Laura	8	1997	5	8	6	2	4	3	6	5	3	7	2	3
Callahan	Laura	8	1998	7	2	10	9	3	0	0	0	0	0	0	0
Dodsworth	Anne	9	1996	0	0	0	0	0	0	2	0	0	2	0	1
Dodsworth	Anne	9	1997	1	0	1	2	1	3	1	1	2	1	3	3
Dodsworth	Anne	9	1998	5	4	6	4	0	0	0	0	0	0	0	0

--Klientide arv aasta ja kuu lõikes - ROLLUP
    select TOP 40 Orders.EmployeeID as Töötaja,  LastName as Surname,Firstname as FirstName,
    ISNULL(CAST(YEAR(OrderDate) as varchar), 'Total') as OrderYears,
    ISNULL(CAST(Month(OrderDate) as Varchar),'Total') AS  OrderMonths,COUNT(OrderID) as OrdersCOUNT
    from Orders JOIN [Northwind].[dbo].[Employees] ON Orders.EmployeeID = [Northwind].[dbo].[Employees].EmployeeID
    GROUP BY Orders.EmployeeID,  LastName ,Firstname, YEAR(OrderDate),  Month(OrderDate) WITH ROLLUP

(40 rows affected)

Total execution time: 00:00:00.019

Töötaja	Surname	FirstName	OrderYears	OrderMonths	OrdersCOUNT
1	Davolio	Nancy	1996	7	1
1	Davolio	Nancy	1996	8	5
1	Davolio	Nancy	1996	9	5
1	Davolio	Nancy	1996	10	2
1	Davolio	Nancy	1996	11	4
1	Davolio	Nancy	1996	12	9
1	Davolio	Nancy	1996	Total	26
1	Davolio	Nancy	1997	1	3
1	Davolio	Nancy	1997	2	2
1	Davolio	Nancy	1997	3	5
1	Davolio	Nancy	1997	4	1
1	Davolio	Nancy	1997	5	5
1	Davolio	Nancy	1997	6	4
1	Davolio	Nancy	1997	7	7
1	Davolio	Nancy	1997	8	3
1	Davolio	Nancy	1997	9	8
1	Davolio	Nancy	1997	10	7
1	Davolio	Nancy	1997	11	3
1	Davolio	Nancy	1997	12	7
1	Davolio	Nancy	1997	Total	55
1	Davolio	Nancy	1998	1	9
1	Davolio	Nancy	1998	2	9
1	Davolio	Nancy	1998	3	11
1	Davolio	Nancy	1998	4	8
1	Davolio	Nancy	1998	5	5
1	Davolio	Nancy	1998	Total	42
1	Davolio	Nancy	Total	Total	123
1	Davolio	NULL	Total	Total	123
1	NULL	NULL	Total	Total	123
2	Fuller	Andrew	1996	7	1
2	Fuller	Andrew	1996	8	2
2	Fuller	Andrew	1996	9	5
2	Fuller	Andrew	1996	10	2
2	Fuller	Andrew	1996	11	2
2	Fuller	Andrew	1996	12	4
2	Fuller	Andrew	1996	Total	16
2	Fuller	Andrew	1997	1	4
2	Fuller	Andrew	1997	2	1
2	Fuller	Andrew	1997	3	4
2	Fuller	Andrew	1997	4	3

--Tellimuste arv aasta lõikes - PIVOT
SELECT OrderYears as 'Aasta',[1] 'Jaan',[2]'Veebr',[3]'Marts',[4]'Aprill',[5]'Mai',[6]'Juuni',[7]'Juuli', [8] 'August',[9] as 'September',[10] as 'Oktoober',[11] 'November',[12] 'Detsember'
FROM (SELECT OrderID, year(Orderdate) as OrderYears, month(orderdate) as OrderMonths
        FROM Orders ) as Andmed
PIVOT
    (COUNT(OrderID) for OrderMonths IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as Tabel

(3 rows affected)

Total execution time: 00:00:00.052

Aasta	Jaan	Veebr	Marts	Aprill	Mai	Juuni	Juuli	August	September	Oktoober	November	Detsember
1998	55	54	73	74	14	0	0	0	0	0	0	0
1996	0	0	0	0	0	0	22	25	23	26	25	31
1997	33	29	30	31	32	30	33	33	37	38	34	48