###### tags: `sql`
# SQL calculations (course3)
Calculations performed on multiple rows of a table are called **aggregates**.
* COUNT() : count the number of rows
* SUM() : sums of values in a column
* MAX() / MIN()
* AVG() : average
* ROUND()
* GROUP BY : used with aggregate functions to combine data from columns
* HAVING : limit the results of a query based on an aggregate property
### 1. **Count**
``` sql=
SELECT COUNT(*)
FROM table_name;
-- ex: count how many free apps are in the table
SELECT COUNT(*)
FROM fake_apps
WHERE price = 0.0;
```
### 2. **Sum**
``` sql=
SELECT MAX(downloads)
FROM fake_apps;
```
### 3. **Max / Min**
``` sql=
SELECT MIN(downloads)
FROM fake_apps;
SELECT MAX(price)
FROM fake_apps;
```
### 4. **Avg**
``` sql=
SELECT AVG(price)
FROM fake_apps;
```
### 5. **Round**
This 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.
``` sql=
SELECT ROUND(AVG(price),2)
FROM fake_apps;
```
### 6.**Group by**
To calculate an aggregate for data with certain characteristics.
#### (1)
``` sql=
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;
--簡化為
SELECT year,
AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;
--ex:
SELECT price, COUNT(*)
FROM fake_apps
GROUP BY price;
```
``` sql=
SELECT category, SUM(downloads)
FROM fake_apps
GROUP BY category;
```
#### (2) GROUP BY a calculation done on a column.
``` sql=
--we 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);
--above query may be time-consuming to write and more prone to error
--the following is equivalent to the previous one
SELECT ROUND(imdb_rating),
COUNT(name)
FROM movies
GROUP BY 1 --1 is the first column selected, and so on
ORDER BY 1;
```
Here, the **1** refers to the first column in our SELECT statement, ROUND(imdb_rating).
``` sql=
SELECT category,
price,
AVG(downloads)
FROM fake_apps
GROUP BY 1, 2;
```
這裡1跟2代表的不是table的第一、第二行，而是SELECT的第一跟第二順位。
### 7. **Having**
To filter which groups to include and which to exclude.
* <font color="#900C3F">HAVING</font> is very similar to <font color="#900C3F">WHERE</font>. In fact, all types of <font color="#900C3F">WHERE</font> clauses you learned about thus far can be used with <font color="#900C3F">HAVING</font>.
* <font color="#900C3F">HAVING</font> statement always comes after <font color="#900C3F">GROUP BY</font>, but before <font color="#900C3F">ORDER BY</font> and <font color="#900C3F">LIMIT</font>.
* WHERE是過濾row，HAVING過濾group
``` sql=
SELECT price,
ROUND(AVG(downloads)),
COUNT(*)
FROM fake_apps
GROUP BY 1
HAVING COUNT(price) > 10;
```
Sign in with Wallet
Connect another wallet