[SQL Intermediate] Aggregate Functions - PART 1

* Count

1) counting all rows

SELECT COUNT(*)

FROM table1

-> count(1) is the same as count(*)

2) counting individual columns

SELECT COUNT(high)

FROM table1

: only counts the values in column 'high'

3) counting non-numerical columns

SELECT COUNT(date)

FROM table1

-> using 'as' 

SELECT COUNT(date) AS count_of_date

FROM table1

-> if there's a space then use double quotes

SELECT COUNT(date) as "count of date"

FROM table1

* SUM

SELECT SUM(volume)

FROM table1

In 'SUM', it treats null values as 0. 

*MIN/MAX

SELECT MIN(volume) AS min_volume,

SELECT MAX(volume) AS max_volume

FROM table1

*AVG

SELECT AVG(high)

FROM table1

WHERE high IS NOT NULL

*GROUP BY

- Allows to separate table into different groups

SELECT  year, 

        month,

        sum(volume)

from table1

group by month, year

order by year, month

-> using GROUP BY with ORDER BY 

the data is displayed chronologically

*HAVING

Having is a "clean" way to filter a query that has been aggregated. This is commonly done in a subquery. 

select year, month, max(high) as month_high

from tutorial.aapl_historical_stock_price 

group by year, month

having max(high)>450

order by year, month

Query Clause Order

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. HAVING

6. ORDER BY