[SQL] 1. Aggregate Functions

We are going to use the following database: 

SELECT * FROM tutorial.aapl_historical_stock_price

tutorial.aapl_historical_stock 데이터베이스 

1. Aggregate functions in SQL

SELECT / HAVING 에 사용

• COUNT: 특정 칼럼에 몇개의 행이 있는지 count
• SUM: 특정 칼럼에 있는 값의 총합
• MIN & MAX: 특정 칼럼의 가장 작은(earliest date, smallest, closest to "A") / 큰 값
• AVG: calculates average of group of selected values 
  Basic Tutorial 에서 말한 것들은 across rows. 여기는 칼럼 전체에 관한 계산

2. Additional Basics of SQL

• GROUP BY : COUNT, AVG, SUM: aggregate across entire table. What if you want to aggregate only part of the table? Use groupby 
• ORDER BY 
• HAVING : filter the groupby result. WHERE clause doesn't work bc it doesn't allow you to filter aggregate columns 

---

 

1.1 Counting all rows 
SELECT COUNT(*)
  FROM tutorial.aapl_historical_stock_price 
  -- returns total number of rows 

 

1.2 Counting individual columns 
SELECT COUNT(high)
  FROM tutorial.aapl_historical_stock_price
  -- counts all the rows except those with NULL

Q: Write a query to count the number of non-null rows in the low column 
SELECT COUNT(low)AS low
  FROM tutorial.aapl_historical_stock_price
-- If you don't specify the column name, result will be just shown as COUNT

1.3 Counting non-numerical columns
SELECT COUNT(date) AS count_of_date
  FROM tutorial.aapl_historical_stock_price
SELECT COUNT(date) AS "Count Of Date" 
  FROM tutorial.aapl_historical_stock_price
-- if you need to use spaces, use double quotes 

Q: write a query that determines counts of every single column
Which column has the most null values?
SELECT COUNT(year) AS year,
       COUNT(month) AS month,
       COUNT(open) AS open,
       COUNT(high) AS high,
       COUNT(low) AS low,
       COUNT(close) AS close,
       COUNT(volume) AS volume
  FROM tutorial.aapl_historical_stock_price

2. SUM

- COUNT 는 그냥 행의 갯수를 계산
- SUM 은 실제 값에 관한 계산 
**Aggregate vertically! If you want to calculate across rows, use simple arithmetic

SELECT SUM(volume)
  FROM tutorial.aapl_historical_stock_price
  
Q: Write a query to calculate the average opening price
SELECT SUM(open)/COUNT(open) AS "Average Opening Price"
  FROM tutorial.aapl_historical_stock_price

3. MIN/MAX

SELECT MIN(volume) AS min_volume, 
       MAX(volume) AS max_volume
  FROM tutorial.aapl_historical_stock_price

Q: What ws Apple's lowest stock price?
SELECT MIN(low)
  FROM tutorial.aapl_historical_stock_price

Q: Highest single day increase in stock price?
SELECT MAX(close-open)
  FROM tutorial.aapl_historical_stock_price

4. AVG

**Limitations: ONLY can use for numerical columns 
IGNORES NULLS completely -> You cannot treat null value as 0 

SELECT AVG(high)
  FROM tutorial.aapl_historical_stock_price
  WHERE high IS NOT NULL 
SELECT AVG(high)
  FROM tutorial.aapl_historical_stock_price
-- 2 queries above have the same result

 

Q: Write a query that calcualtes the average daily trade volumns for Apple Stock
SELECT AVG(volume) as avg_volume
  FROM tutorial.aapl_historical_stock_price

---

5. GROUP BY 

5.1 SQL GROUP BY clause
SELECT year,
       month,
       COUNT(*) AS count
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year, month

Q: Calculate the total number of shares traded each month. Order results chronologivally.
SELECT year, 
      month,
      SUM(volume) AS volume_sum
  FROM tutorial.aapl_historical_stock_price
  GROUP BY year, month
  ORDER BY year, month

5.2 GROUP BY column numbers
SELECT year, month, COUNT(*) AS count
  FROM tutorial.aapl_historical_stock_price
  GROUP BY 1,2
  
5.3 Using GROUP BY with ORDER BY
SELECT year, month, COUNT(*) AS count
  FROM tutorial.aapl_historical_stock_price
  GROUP BY year, month
  ORDER BY year, month 
  -- ORDER BY month, year : totally different result
  
Q: Write a query to calculate the average daily price change in Apple stock, grouped by year
SELECT year, AVG(high - low) AS avg_daily_change
  FROM tutorial.aapl_historical_stock_price
  GROUP BY year
  ORDER BY year

Q: Write a query that calculates the lowest and highest prices that Apple stock achieved each month
SELECT year, month, 
       MIN(low) AS min_price_monthly,
       MAX(high) AS max_price_monthly 
  FROM tutorial.aapl_historical_stock_price
  GROUP BY month, year
  ORDER BY year, month 

6. HAVING

SELECT year, month, MAX(high) AS month_high
  FROM tutorial.aapl_historical_stock_price
  GROUP BY year, month
  HAVING MAX(high) > 400
  ORDER BY year, month