GROUP BY์ ์ฌ์ฉ๋ฒ
GROUP๋ ํน์ ์ปฌ๋ผ์ ๊ธฐ์ค์ผ๋ก ์ง๊ณ๋ฅผ ๋ด๋๋ฐ ์ฌ์ฉ๋๋ค.
์ผ๋จ ์๋ฅผ ๋ค์ด ๋ณด๋๋ก ํ๊ฒ ์ต๋๋ค.
์๋์ ๊ฐ์ด ํ๊ธ ์ ์ฒด์ ์ฑ์ ์ ๋ํ๋ด๋ ํ ์ด๋ธ์ด ์์ต๋๋ค.
class๋ ๋ฐ ๋ช ์ด๋ฉฐ name ํ์ ์ด๋ฆ score๋ ํด๋น ํ์์ ์ฑ์ ์ ์ ์ ๋๋ค.

์ด์ ์ด๋ฐ ํ๊ธ ์ ์ฒด ์ฑ์ ์ ํ๊ธ ๋ณ๋ก ์ดํฉ ์ง๊ณ๋ฅผ ๋ด๊ณ ์ถ์ผ๋ฉด group by๋ฅผ ์ด์ฉํฉ๋๋ค.
SELECT CLASS, SUM(SCORE) FROM TBL_REPORT_CARD GROUP BY CLASS; |
๊ทธ๋ผ ๋ค์๊ณผ ๊ฐ์ด ํ๊ธ๋ณ๋ก ์ ์ ์ดํฉ์ ๊ฒฐ๊ณผ๊ฐ ์ถ๋ ฅ ๋ฉ๋๋ค.

Group by๋ฅผ ์ฌ์ฉ ํ๋ ๋ฐฉ๋ฒ์ ์๋์ ๊ฐ์ต๋๋ค.
SELECT [GROUP BY ์ ์ ์ง์ ๋ ์ปฌ๋ผ1] [GROUP BY๋ณ๋ก ์ง๊ณํ ๊ฐ] FROM [ํ ์ด๋ธ ๋ช ] GROUP BY [ ๊ทธ๋ฃน์ผ๋ก ๋ฌถ์ ์ปฌ๋ผ ๊ฐ ] |
Having์ ์ฌ์ฉ๋ฒ
Having ์ ์ Group by๋ก ์ง๊ณ๋ ๊ฐ ์ค where ์ ์ฒ๋ผ ํน์ ์กฐ๊ฑด์ ์ถ๊ฐํ๋ค๊ณ ์๊ฐ ํ์๋ฉด ๋ฉ๋๋ค.
์๋ฅผ ๋ค์ด ํ๊ธ๋ณ ์ดํฉ ๊ฒฐ๊ณผ ์ค์ 150์ ์ด์์ธ ๊ฒ๋ง ์ถ๋ ฅ ํ๊ณ ์ถ์ผ๋ฉด ๋ค์๊ณผ ๊ฐ์ด ์์ ๋ฅผ ์์ฑํ๋ฉด ๋ฉ๋๋ค.
SELECT CLASS, SUM(SCORE) FROM TBL_REPORT_CARD GROUP BY CLASS HAVING SUM(SCORE) > 150 ; |
๊ฒฐ๊ณผ ๊ฐ์ ์๋์ ๊ฐ์ต๋๋ค.

Having ์ ์ ์ฌ์ฉ ํ๋ ๋ฐฉ๋ฒ์ ์๋์ ๊ฐ์ต๋๋ค.
SELECT [GROUP BY ์ ์ ์ง์ ๋ ์ปฌ๋ผ1] [GROUP BY๋ณ๋ก ์ง๊ณํ ๊ฐ] FROM [ํ ์ด๋ธ ๋ช ] GROUP BY [ ๊ทธ๋ฃน์ผ๋ก ๋ฌถ์ ์ปฌ๋ผ ๊ฐ ] HAVING [์กฐ๊ฑด ์ถ๊ฐ] ; |
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
115
116
117
118
119
120
121
|
/*
ORDER BY == SORTING
SELECT COLUM VALUE SUB QUERY
FROM TABLE SUB QUERY
WHERE IF
ORDER BY COLUMN ASC DESC
*/
DESC EMP;
SELECT ename,sal
FROM emp
ORDER BY sal ASC;--์ค๋ฆ์ฐจ์์ผ๋ก ์ ๋ ฌ
SELECT ename,sal
FROM emp
ORDER BY sal DESC; -- ๋ด๋ฆผ์ฐจ์์ผ๋ก
--alias
SELECT empno, ename, sal*12 AS annsal
FROM emp
ORDER BY annsal DESC;
--๋ณด๋์ค ์๋ ์ฌ๋ ์์ผ๋ก
SELECT ename, comm
FROM emp
ORDER BY comm NULLS FIRST;
SELECT employee_id,job_id, salary
FROM employees
ORDER BY job_id ASC, salary DESC;
--months_between :
SELECT sysdate
FROM DUAL;
SELECT MONTHS_BETWEEN('2021-06-23', '2021-02-09')
FROM dual;
--add_months : ํน์ ๋ ์ง ์์ ์ ์๋ฅผ ๋ํ ๋ค์ ํด๋น ๋ ์ง๋ฅผ ๋ฐํํ๋ค.
SELECT ADD_MONTHS('2021-02-09', 4)
FROM DUAL;
-- ๋
์ ์ผ
SELECT EXTRACT(year from TO_DATE('210623','YYMMDD')) AS ์ฐ๋,
EXTRACT(month from TO_DATE('210623','YYMMDD')) AS ์,
EXTRACT(day from TO_DATE('210623','YYMMDD')) AS ์ผ
FROM DUAL;
-- ์ ๋ถ ์ด
SELECT EXTRACT(hour from CAST(SYSDATE AS TIMESTAMP)) AS ์,
EXTRACT(MINUTE from CAST(SYSDATE AS TIMESTAMP)) AS ๋ถ,
EXTRACT(SECOND from CAST(SYSDATE AS TIMESTAMP)) AS ์ด
FROM DUAL;
SELECT EXTRACT(hour from CAST(TO_DATE('2021-06-23 09:42:02','YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP)) AS ์,
EXTRACT(MINUTE from CAST(SYSDATE AS TIMESTAMP)) AS ๋ถ,
EXTRACT(SECOND from CAST(SYSDATE AS TIMESTAMP)) AS ์ด
FROM DUAL;
/*
GROUP BY : ๊ทธ๋ฃน์ผ๋ก ๋ฌถ๋ ๊ธฐ๋ฅ
*/
SELECT DISTINCT job_id
FROM employees;
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY department_id ASC;
/*
ํต๊ณ - GROUP FUNCTION
count
sum
avg
max
min
*/
--
SELECT COUNT(salary), count(*), sum(salary), avg(salary), max(salary), min(salary)
FROM employees
WHERE job_id = 'IT_PROG';
SELECT job_id, count(*), sum(salary), avg(salary) --count ์
๋ฌด๋ณ๋ก ๋ช๋ช
์ด๋, ํฉ๊ณ๋ ๋ช์ด๋, ํ๊ท ์ ๋ช์ด๋
FROM employees
GROUP BY job_id
ORDER BY job_id;
SELECT job_id, sum(SALARY)
FROM employees
GROUP BY job_id
HAVING SUM(salary) >=100000; --having์ ์ ๊ทธ๋ฃนํ๊ณ ์ฌ์ฉ
๊ธ์ฌ๊ฐ 5000์ด์ ๋ฐ๋ ์ฌ์๋ง์ผ๋ก ํฉ๊ณ๋ฅผ ๋ด์ ์
๋ฌด(JOB_ID)๋ก ๊ทธ๋ฃนํํ์ฌ
๊ธ์ฌ์ ํฉ๊ผ๊ฐ 20000์ ์ด๊ณผํ๋ ์
๋ฌด๋ช
์ ๊ตฌํ๋ผ
SELECT job_id, sum(salary)
FROM employees
WHERE salary >=5000
GROUP BY job_id
HAVING SUM(salary) > 20000;
|
cs |
'๐ฆ ๋ฐ์ดํฐ ๋ฒ ์ด์ค (DB) ๐ฆ > ๐ พ ์ค๋ผํด DB & SQL' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
ORDER BY๋ฌธ - ๋ฌธ์ 1 (0) | 2021.02.10 |
---|---|
Power, Trunc, Trim, Round๋ฑ ๊ฐ์ข ํจ์ 1# (0) | 2021.02.10 |
SELECT ๋ฌธ - ์ค๊ฐ์ ๊ฒ ์์ (0) | 2021.02.08 |
SELECT๋ฌธ - 2 (0) | 2021.02.08 |
SELECT ๋ฌธ - 1 (0) | 2021.02.08 |
๋๊ธ