Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

AVG

예전에 잠깐 나온적 있는 AVG함수이다. AVERAGE

• 기본사용. Q. Calculate the average released_year across all books

mysql> SELECT AVG(released_year) FROM books;
+--------------------+
| AVG(released_year) |
+--------------------+
|          1999.7222 |
+--------------------+
1 row in set (0.00 sec)

Q. Calculate the average pages across all books

mysql> SELECT AVG(pages) FROM books;
+------------+
| AVG(pages) |
+------------+
|   342.1111 |
+------------+
1 row in set (0.00 sec)

• Q. Calculate the average stock quantity for books released in the same year

정말 간단하게 AVG칼럼만 표현한다면

mysql> SELECT AVG(stock_quantity) FROM books GROUP BY released_year;
+---------------------+
| AVG(stock_quantity) |
+---------------------+
|             66.0000 |
|             43.0000 |
|             97.0000 |
|            154.0000 |
|             26.0000 |
|             68.0000 |
|             55.0000 |
|            195.5000 |
|             23.0000 |
|             12.0000 |
|             49.0000 |
|             95.0000 |
|            172.0000 |
|             92.0000 |
|             29.0000 |
|           1000.0000 |
+---------------------+
16 rows in set (0.00 sec)

더 보기 쉽게 년도와 함께

mysql> SELECT released_year, AVG(stock_quantity) FROM books GROUP BY released_year;
+---------------+---------------------+
| released_year | AVG(stock_quantity) |
+---------------+---------------------+
|          2003 |             66.0000 |
|          2016 |             43.0000 |
|          1996 |             97.0000 |
|          2012 |            154.0000 |
|          2013 |             26.0000 |
|          2000 |             68.0000 |
|          2010 |             55.0000 |
|          2001 |            195.5000 |
|          1981 |             23.0000 |
|          1989 |             12.0000 |
|          1985 |             49.0000 |
|          1945 |             95.0000 |
|          2004 |            172.0000 |
|          2005 |             92.0000 |
|          2014 |             29.0000 |
|          2017 |           1000.0000 |
+---------------+---------------------+
16 rows in set (0.00 sec)

년도별로 정렬(ORDER BY)

mysql> SELECT released_year, AVG(stock_quantity) 
	FROM books GROUP BY released_year ORDER BY released_year;
+---------------+---------------------+
| released_year | AVG(stock_quantity) |
+---------------+---------------------+
|          1945 |             95.0000 |
|          1981 |             23.0000 |
|          1985 |             49.0000 |
|          1989 |             12.0000 |
|          1996 |             97.0000 |
|          2000 |             68.0000 |
|          2001 |            195.5000 |
|          2003 |             66.0000 |
|          2004 |            172.0000 |
|          2005 |             92.0000 |
|          2010 |             55.0000 |
|          2012 |            154.0000 |
|          2013 |             26.0000 |
|          2014 |             29.0000 |
|          2016 |             43.0000 |
|          2017 |           1000.0000 |
+---------------+---------------------+
16 rows in set (0.00 sec)