๐Ÿงท SQL ๋ฌธ๋ฒ• 2

may_soouuยท2021๋…„ 5์›” 11์ผ
0
post-thumbnail

1. COUNT ๊ฐฏ์ˆ˜ ์„ธ๊ธฐ

SELECT COUNT (*)
FROM ํ…Œ์ด๋ธ” ์ด๋ฆ„;

 > Products ํ…Œ์ด๋ธ”์— ์žˆ๋Š” ๋ ˆ์ฝ”๋“œ์˜ ๊ฐฏ์ˆ˜๋ฅผ ์„ธ๋ผ
 
 
ํŠน์ • ์นผ๋Ÿผ๋งŒ ์„ธ๊ณ  ์‹ถ๋‹ค๋ฉด?
SELECT COUNT(์„ธ๊ณ  ์‹ถ์€ ์นผ๋Ÿผ)
FROM ํ…Œ์ด๋ธ” ์ด๋ฆ„


์ค‘๋ณต์ œ๊ฑฐ ํ•˜๊ณ  ์„ผ๋‹ค๋ฉด?
SELECT COUNT(DISTINCT ์นผ๋Ÿผ)
FROM ํ…Œ์ด๋ธ” ์ด๋ฆ„

2. SUM / AVG

ํ•ฉ๊ณ„
SELECT SUM(์นผ๋Ÿผ)
FROM ํ…Œ์ด๋ธ” ์ด๋ฆ„

ํ‰๊ท 
SELECT AVG(์นผ๋Ÿผ)
FROM ํ…Œ์ด๋ธ” ์ด๋ฆ„

๋งŒ์•ฝ ํ‰๊ท  ๋‚ด๋ ค๋Š” ๊ฐ’์— NULL ๊ฐ’์ด ์žˆ์„ ๋•Œ
  1) NULL ์„ ๋ฐ์ดํ„ฐ๊ฐ€ ์—†๋‹ค๊ณ  ํ•ด์„ํ•˜๊ณ  ํ‰๊ท  ๋‚ธ๋‹ค๋ฉด

  SELECT AVG(์นผ๋Ÿผ)
  FROM ํ…Œ์ด๋ธ” ์ด๋ฆ„


  2) NULL ์„ 0์œผ๋กœ ํ•ด์„ํ•œ๋‹ค๋ฉด,
  
  SELECT SUM(์นผ๋Ÿผ)/count(*)
  FROM ํ…Œ์ด๋ธ” ์ด๋ฆ„

3. ์ตœ๋Œ€ / ์ตœ์†Œ

SELECT min(price)
SELECT max(price)

4. ๊ทธ๋ฃน - GROUP BY

ex. ๊ณต๊ธ‰์ž๋ณ„ ํ‰๊ท  ๊ฐ€๊ฒฉ

SELECT SupperlierID, AVG(Price)
FROM Products
GROUP BY SupperlierID

5. ๊ทธ ์™ธ

ํ‰๊ท  ๋‚ธ ๊ฒฐ๊ณผ์— ๋Œ€ํ•ด ํ•„ํ„ฐ๋ง์„ ๊ฑธ๊ณ  ์‹ถ์œผ๋ฉด

SELECT SupperlierID, AVG(Price)
FROM Products
GROUP BY SupperlierID
HAVING AVG(Price) >= 100 

 > WHERE ์ ˆ ์“ฐ๋ฉด ์•ˆ๋จ !! HAVING ์จ์•ผ ํ•จ
 
------------------------------------------------------------
select SupperlierID, 
       AVG(Price) AS avg_price
      # as ๋Š” ๋ณ„๋ช… ๋ถ™์ด๊ธฐ

์ถœ์ฒ˜_์ธํ”„๋Ÿฐ ์ธ๊ฐ•

profile
back-end ๊ฐœ๋ฐœ์ž

0๊ฐœ์˜ ๋Œ“๊ธ€