๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ

๐Ÿ“ฆ ๋ฐ์ดํ„ฐ ๋ฒ ์ด์Šค (DB) ๐Ÿ“ฆ/๐Ÿ…พ ์˜ค๋ผํด DB & SQL

ORDER BY , GROUP BY ๋ฌธ

by Meteora_ 2021. 2. 10.
728x90

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

๋Œ“๊ธ€