SQL Aggregation Functions

COUNT
SUM
Min and Max
Average

NULL
: no data, different from 0 or no data
: it is a property,
so in a query, after where clause, it should be:
IS NULL
IS NOT NULL

COUNT
only counts values that are not NULL
SELECT COUNT(*)
FROM accounts;

SUM
- Can only be used with numerical values
- NULL values are considered as '0'

MIN/MAX
- Min will return the lowest number
- Max will return the highest number/ the latest date/ the non-numerical value closest alphabetically to 'z'

AVG
- Ignores NLLL values
- Can only be used for numerical data

GROUP BY
- Group by can be used to aggregate data within subsets of the data. For example, grouping for different accounts, different regions, or different sales representatives.
- Any column in the SELECT statement that is not within an aggregator must be in the GROUP BY clause.
- The GROUP BY always goes between WHERE and ORDE BY.
- ORDER BY works like SORT in spreadsheet software.

DISTINCT
- returns unique rows

HAVING
- 'clean' way to filter a query that has been aggregated, but this is also commonly done using a subquery.
- Any time you want to perform a WHERE on an element of your query that was created by an aggregate, you need to use HAVING instead.

DATE
 - DATE TRUNC

CASE
SELECT account_id, CASE WHEN standard_qty = 0 OR standard_qty IS NULL THEN 0
                        ELSE standard_amt_usd/standard_qty END AS unit_price
FROM orders

LIMIT 10;