Mysql - AVG, COUNT, SUM, MIN, MAX, GROUP BY, HAVING, DATE_FORMAT

SELECT AVG(단가) FROM 제품;

SELECT COUNT(제조업체) FROM 제품;

SELECT COUNT(DISTINCT 제조업체) FROM 제품;

SELECT * FROM EMP;
SELECT COUNT(*) FROM EMP;

SELECT COUNT(*) FROM EMP WHERE MGR IS NOT NULL;

SELECT SUM(SAL) FROM EMP WHERE SAL <= 2000;

SELECT COUNT(*), AVG(SAL) FROM EMP WHERE JOB = 'MANAGER' ;

SELECT MIN(SAL) FROM EMP ;

SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 ;

SELECT COUNT(DISTINCT JOB) FROM EMP ;

SELECT * FROM 주문;
SELECT SUM(수량) FROM 주문
GROUP BY 주문제품;

SELECT 주문제품 FROM 주문
GROUP BY 주문제품;
-- HAVING SUM(수량) >= 50;

SELECT 제조업체, COUNT(*) , MAX(단가)
FROM 제품
GROUP BY 제조업체;

SELECT 주문제품, 주문고객, COUNT(*)
FROM 주문
GROUP BY 주문제품, 주문고객;

SELECT 주문제품, COUNT(*)
FROM 주문
GROUP BY 주문제품;

--------------------------------------------

SELECT DEPTNO, COUNT(*) FROM EMP
GROUP BY DEPTNO;

SELECT JOB, COUNT(*) FROM EMP
GROUP BY JOB;

SELECT JOB, AVG(SAL) FROM EMP
GROUP BY JOB;

SELECT DEPTNO, SUM(SAL) FROM EMP
GROUP BY DEPTNO;

SELECT COUNT(*), DEPTNO, JOB FROM EMP
GROUP BY DEPTNO, JOB;

SELECT JOB, COUNT(*) FROM EMP
GROUP BY JOB
HAVING COUNT(*) >= 3;

SELECT DEPTNO, COUNT(*) FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) <= 4;

SELECT MGR FROM EMP
WHERE MGR IS NOT NULL
GROUP BY MGR
HAVING COUNT(*) = 1;

SELECT * FROM EMP;

-- (방법1) 입사월별 사원통계
SELECT SUBSTR(HIREDATE, 1,7), COUNT(*)
FROM EMP
GROUP BY SUBSTR(HIREDATE, 1,7)
ORDER BY SUBSTR(HIREDATE, 1,7) DESC;

-- (방법2) 입사월별 사원통계
SELECT DATE_FORMAT(HIREDATE, '%Y-%m'), COUNT(*) 
FROM EMP
GROUP BY DATE_FORMAT(HIREDATE, '%Y-%m') 
ORDER BY DATE_FORMAT(HIREDATE, '%Y-%m') DESC;

-----

SELECT DATE_FORMAT(VST_TIME,'%Y-%m') AS 방문월, 
VST_PATH AS 방문경로, 
CASE VST_PATH 
WHEN '1' THEN '소개사이트'
WHEN '2' THEN 'BTO' END AS '경로',
COUNT(*) AS 방문자수
FROM visitor_tb
GROUP BY DATE_FORMAT(VST_TIME,'%Y-%m'), VST_PATH 
ORDER BY DATE_FORMAT(VST_TIME,'%Y-%m'), VST_PATH;

------

CREATE TABLE NOODLE (
NUM INT
, NAME VARCHAR(50)
, COMPANY VARCHAR(10)
, KIND VARCHAR(1)
, PRICE INT
, E_DATE DATETIME
);

INSERT INTO NOODLE VALUES (1, '안성탕면', '농심', 'M', 700, NOW() +
INTERVAL 3 MONTH);
INSERT INTO NOODLE VALUES (1, '김치라면', '삼양', 'M', 700, NOW() +
INTERVAL 3 MONTH);
INSERT INTO NOODLE VALUES (1, '진라면', '오뚜기', 'C', 800, NOW() + INTERVAL
3 MONTH);
INSERT INTO NOODLE VALUES (1, '꼬꼬면', '팔도', 'C', 1000, NOW() + INTERVAL
3 MONTH);
INSERT INTO NOODLE VALUES (2, '신라면', '농심', 'C', 1100, NOW() + INTERVAL
3 MONTH);
INSERT INTO NOODLE VALUES (2, '삼양라면', '삼양', 'M', 700, NOW() +
INTERVAL 3 MONTH);
INSERT INTO NOODLE VALUES (3, '너구리', '농심', 'M', 850, NOW() + INTERVAL
3 MONTH);
INSERT INTO NOODLE VALUES (3, '나가사키짬뽕', '삼양', 'C', 1300, NOW()
+ INTERVAL 3 MONTH);
INSERT INTO NOODLE VALUES (4, '짜파게티', '농심', 'C', 1200, NOW() +
INTERVAL 3 MONTH);

---

SELECT COMPANY, COUNT(*) AS COUNT
FROM NOODLE
GROUP BY COMPANY;

SELECT COMPANY, CONCAT(COUNT(*), '개') AS COUNT
FROM NOODLE
GROUP BY COMPANY;

SELECT COMPANY, COUNT(*) AS COUNT
FROM NOODLE
GROUP BY COMPANY
ORDER BY COUNT ASC;

SELECT COMPANY, COUNT(*) AS COUNT
FROM NOODLE
GROUP BY COMPANY
HAVING COUNT(*) >= 3;

SELECT NAME, COMPANY, DATE_FORMAT(E_DATE, "%Y년 %m월 %d일") AS E_DATE
FROM NOODLE
WHERE KIND = 'M' AND PRICE >= 800;

SELECT CONCAT(MAX(PRICE), '원') AS PRICE
FROM NOODLE
WHERE KIND = 'C';

SELECT COMPANY, CONCAT(MIN(PRICE), '원') AS PRICE
FROM NOODLE
GROUP BY COMPANY;

SELECT COMPANY, CONCAT(SUM(PRICE), '원') AS '제조사별 합계'
FROM NOODLE
GROUP BY COMPANY
ORDER BY SUM(PRICE) ASC;

SELECT COMPANY, ROUND(AVG(PRICE), 0) AS '제조사별 평균'
FROM NOODLE
GROUP BY COMPANY
ORDER BY AVG(PRICE) DESC;