SQL_DEV/EDUCATION/GROUP_BY.sql at master · Brezencat/SQL_DEV · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114


SELECT firstname + middlename AS FULLNAME
	,	division_id
	,	YEAR(dt_registration) AS YEAR_REGISTRATION
	, ID
FROM dtln.dbo.worker
WHERE [actual] = 1
;

SELECT  division_id
	,	count(*) AS cn_name
	,	count(division_id) AS cn_division
FROM dtln.dbo.worker
WHERE [actual] = 1
GROUP BY division_id
union all
SELECT null
	,	count(*) AS cn_name
	,	null
FROM dtln.dbo.worker
WHERE [actual] = 1


SELECT  division_id
	,	YEAR(dt_registration) AS YEAR_REGISTRATION
	,	count(*) AS cn_name
FROM dtln.dbo.worker
WHERE [actual] = 1
GROUP BY division_id
	,	 YEAR(dt_registration)
;

SELECT  division_id
	,	YEAR(dt_registration) AS YEAR_REGISTRATION
	,	count(*) AS cn_name
FROM dtln.dbo.worker
WHERE [actual] = 1
GROUP BY
ROLLUP (division_id
	,	 YEAR(dt_registration)
	)
;

SELECT  division_id
	,	YEAR(dt_registration) AS YEAR_REGISTRATION
	,	count(*) AS cn_name
FROM dtln.dbo.worker
WHERE [actual] = 1
GROUP BY
ROLLUP (YEAR(dt_registration)
	,	division_id
	)
;


SELECT  division_id
	,	YEAR(dt_registration) AS YEAR_REGISTRATION
	,	count(*) AS cn_name
FROM dtln.dbo.worker
WHERE [actual] = 1
GROUP BY
CUBE (YEAR(dt_registration)
	,	division_id
	)
;


SELECT  division_id
	,	YEAR(dt_registration) AS YEAR_REGISTRATION
	,	count(*) AS cn_name
FROM dtln.dbo.worker
WHERE [actual] = 1
GROUP BY
GROUPING SETS (YEAR(dt_registration)
	,	division_id
	)
;


SELECT  division_id
	,	YEAR(dt_registration) AS YEAR_REGISTRATION
	,	count(*) AS cn_name
	,	GROUPING (YEAR(dt_registration)) AS GROUP_AS_YEAR
	,	GROUPING (division_id) AS GROUP_AS_DIVISION
FROM dtln.dbo.worker
WHERE [actual] = 1
GROUP BY
ROLLUP (division_id
	,	 YEAR(dt_registration)
	)
;

SELECT  CASE WHEN 1 in (GROUPING (division)) and 0 in (GROUPING (YEAR(dt_registration)))
			 THEN 'Промежуточный итог'
			 ELSE division
		END AS division
	,	CASE WHEN 1 in (GROUPING (YEAR(dt_registration))) and 0 in (GROUPING (division))
			 THEN 'Промежуточный итог'
			 ELSE CAST(YEAR(dt_registration) as varchar(5))
		END AS YEAR_REGISTRATION
	,	count(*) AS cn_name
FROM dtln.dbo.worker as w
inner join dbo.division as d
	on d.id = w.division_id
WHERE [actual] = 1
GROUP BY
CUBE (division
	, YEAR(dt_registration)
	)
;