Aggregate Function

SQL에서 계산에 이용되는 함수 사용방법에 대해 정리하였습니다.

 

 

---

< Introduction >

We are going to learn how to perform calculations using SQL.
Calculations performed on multiple rows of a table are called aggregates.

 

 

---

< Count >

To calculate how many rows are in a table

 

< Syntax >

SELECT COUNT(columnName)
FROM table_name;

COUNT( ) is a function that takes the name of a column as an argument and counts the number of non-empty values in that column.

 

Example : count how many free apps are in the table.

SELECT COUNT(price) 
FROM fake_apps
WHERE price = 0;

Count(price)

73

 

 

---

< Sum >

To add all values in a particular column.

 

< Syntax >

SELECT SUM(columnName)
FROM table_name;

SUM( ) is a function that takes the name of a column as an argument and returns the sum of all the values in that column.

 

Example : total number of downloads for all of the apps.

SELECT SUM(downloads)
FROM fake_apps;

SUM(downloads)

3322760

 

 

---

< Max / Min >

< Syntax >

SELECT MAX(columnName)
FROM table_name;

SELECT MIN(columnName)
FROM table_name;

The MAX( ) and MIN( ) functions take the name of a column as an argument and return the highest and lowest values in a column, respectively.

 

Example : How many downloads does the most popular app have?

SELECT MAX(downloads)
FROM fake_apps;

MAX(downloads)

31090

 

 

---

< Average >

To calculate the average value of a particular column.

 

< Syntax >

SELECT AVG(columnName)
FROM table_name;

The AVG( ) function takes the name of a column as an argument and returns the average value for that column.

 

Example : the average number of downloads for all the apps in the table.

SELECT AVG(downloads)
FROM fake_apps;

MAX(downloads)

16613.8

 

 

---

< Round >

To make the result table easier to read using the ROUND( ) function.

 

< Syntax >

SELECT ROUND(columnName, integer)
FROM table_name;

ROUND( ) function takes two arguments inside the parenthesis:

1. a column name
2. an integer

: It rounds the values in the column to the number of decimal places specified by the integer.

 

Example : round the average price of an app

SELECT AVG(price)
FROM fake_apps;

AVG(price)

2.02365

SELECT ROUND(AVG(price), 2)
FROM fake_apps;

ROUND(AVG(price), 2)

2.02

 

 

---

< Group By >

To calculate an aggregate for data with certain characteristics.

 

< Syntax >

SELECT columnName
FROM table_name
GROUP BY ____;

GROUP BY is a clause in SQL that is used with aggregate functions. It is used in collaboration with the SELECT statement to arrange identical data into groups.

When you use GROUP BY clause, you will need to aggregate any other column you query.

The GROUP BY statement comes after any WHERE statements, but before ORDER BY or LIMIT.

 

Example1 : want to know the mean IMDb ratings for all movies each year.

Use WHERE statement :

SELECT AVG(imdb_rating)
FROM movies
WHERE year = 1999;
 
SELECT AVG(imdb_rating)
FROM movies
WHERE year = 2000;
 
SELECT AVG(imdb_rating)
FROM movies
WHERE year = 2001;

Use GROUP BY clause :

SELECT year,
   AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;

 

Example2 : want to know how many movies have IMDb ratings that round to 1, 2, 3, 4, 5.

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

However, this query may be time-consuming to write and more prone to error. so, use this :

 

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

NOTE : 

• 1 is the first column selected => ROUND(imdb_rating)
• 2 is the second column selected => COUNT(name)
• so on..

 

 

---

< Having >

When group data using GROUP BY, to filter which groups to include and which to exclude.

 

• We can’t use WHERE here because we don’t want to filter the rows; we want to filter groups.

 

< Syntax >

SELECT columnName
FROM table_name
GROUP BY ____
HAVING ____;

• When we want to limit the results of a query based on values of the individual rows, use WHERE.
• When we want to limit the results of a query based on an aggregate property, use HAVING.

The HAVING statement always comes after GROUP BY, but before ORDER BY and LIMIT.

 

Example : want to know the average downloads (rounded) and the number of apps – at each price point.

SELECT price, 
   ROUND(AVG(downloads)),
   COUNT(*)
FROM fake_apps
GROUP BY 1;

price	ROUND(AVG(downloads))	COUNT(*)
0.0	15762.0	73
0.99	15972.0	43
1.99	16953.0	42
2.99	17725.0	21
3.99	18742.0	9

However, certain price points don’t have very many apps, so their average downloads are less meaningful.

So, restrict the query to price points that have more than 10 apps :

SELECT price, 
   ROUND(AVG(downloads)),
   COUNT(*)
FROM fake_apps
GROUP BY 1
HAVING COUNT(*) > 10;

price	ROUND(AVG(downloads))	COUNT(*)
0.0	15762.0	73
0.99	15972.0	43
1.99	16953.0	42
2.99	17725.0	21