codecamedy의 learn SQL 3. Aggregate Functions

https://www.codecademy.com/learn/learn-sql

SQL Tutorial: Learn SQL For Free | Codecademy

 

1. Count

SELECT COUNT(*)
FROM table_name;

해당하는 조건의 row 갯수.

 

2. Sum

SELECT SUM(downloads)
FROM fake_apps;

해당하는 조건의 row 들의 합.

 

3. Max / Min

SELECT MAX(downloads)
FROM fake_apps;

해당하는 조건의 row 들 중 최대/최소

 

4. Average

SELECT AVG(downloads)
FROM fake_apps;

해당하는 조건의 row 들의 평균

 

5. Round

SELECT ROUND(price, 2)
FROM fake_apps;

value 보기 편하게 반올림해서 바꿔줌. 위의 경우 1.456 -> 1.46

 

6. Group By

SELECT price, COUNT(*) 
FROM fake_apps
GROUP BY price;

SELECT price, COUNT(*) 
FROM fake_apps
where downloads > 20000
GROUP BY price;

 

 

select category, SUM(downloads)
from fake_apps
group by category;

 

sum 같은거 할 때 group by 안하면 의도하지 않은 방향으로 나옴.

select category, SUM(downloads)
from fake_apps;

 

SELECT ROUND(imdb_rating),
   COUNT(name)
FROM movies
GROUP BY ROUND(imdb_rating)
ORDER BY ROUND(imdb_rating);

SELECT ROUND(imdb_rating),
   COUNT(name)
FROM movies
GROUP BY 1
ORDER BY 1;

약어. 위 두개는 같은 뜻임.

 

7. Having

SELECT year,
   genre,
   COUNT(name)
FROM movies
GROUP BY 1, 2
HAVING COUNT(name) > 10;

where는 각각 row에 대한 필터였다면, having은 각각 group용 row를 위한 필터.

 

 

• COUNT(): count the number of rows
• SUM(): the sum of the values in a column
• MAX()/MIN(): the largest/smallest value
• AVG(): the average of the values in a column
• ROUND(): round the values in the column

• GROUP BY is a clause used with aggregate functions to combine data from one or more columns.
• HAVING limit the results of a query based on an aggregate property.