SQL Serise Part III (SQL Aggrega

COUNT

# count the number of rows in a table
SELECT COUNT(*) FROM table;

SUM, MIN, MAX, AVG

# find total amount of col in the table
SELECT SUM(col) AS cl # you can also use some (* + /) operators here
FROM table;

# find the minimal value of col in table, you can also use ORDER BY to implement it
SELECT MIN(col) AS cl
FROM table;

# find the maximal value of col in table, you can also use ORDER BY DESC to implement it
SELECT MAX(col) AS cl
FROM table;

# calculate the average value of col in table
SELECT AVG(col) AS cl
FROM table;

GROUP BY

# The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
SELECT COUNT(id), name
FROM table
GROUP BY name; # will show the amount for each name

DISTINCT

# Use DISTINCT to test if there are any accounts associated with more than one region.
SELECT DISTINCT id, name
FROM table;

HAVING

# The HAVING clause was used with aggregate functions instead of WHERE
SELECT COUNT(id), name
FROM table
GROUP BY name
HAVING COUNT(id) > n;

DATE Functions

# DATE_TRUNC : Truncate to specified precision
# date_trunc('hour', timestamp '2001-02-16 20:38:40') => 2001-02-16 20:00:00
SELECT DATE_TRUNC('month', table1.time_col) cl1, SUM(table1.spent_col) cl2
FROM table1
JOIN table2
ON table1.id = table2.foreign_key_id
WHERE table2.name = 'someting'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

# DATE_PART : Get subfield
# date_part('hour', timestamp '2001-02-16 20:38:40') => 20
SELECT DATE_PART('year', time_col) cl1,  SUM(total_col) cl2
FROM table
GROUP BY 1
ORDER BY 2 DESC;

CASE Statements

# Evaluate conditions and return a value when the first condition is met
# something will be wrong when the total_qty equal 0:
SELECT id, aid, total_price/total_qty AS unit_price
FROM table
LIMIT 10;
# so use CASE statement can avoid the error:
SELECT id, aid, CASE WHEN total_qty = 0 OR total_qty IS NULL THEN 0
                 ELSE total_price/total_qty 
            END AS unit_price
FROM table
LIMIT 10;

# CASE with aggregations
SELECT table1.name, SUM(total_price) total_spent, 
     CASE WHEN SUM(total_price) > 200000 THEN 'top'
          WHEN  SUM(total_price) > 100000 THEN 'middle'
          ELSE 'low' 
     END AS cl
FROM table2
JOIN table1
ON table2.aid = table1.id 
GROUP BY table1.name
ORDER BY 2 DESC;