Currently learning Data Science, here is what I've learnt so far. Pt.3

Hey there! I'm getting used to posting here and really enjoying using this space to post my notes while learning. This time I learned about aggregates and how to structure my SQL queries when using them. Feel free to comment any tips, corrections, or suggestions you might have for me. Thx! ;)

Aggregates - Calculations performed on multiple rows of a table.
COUNT() - count the number of rows
SUM() - the sum of the values in a column
MAX()/MIN() - the largest/smallest value
AVG() - the average of the values in a column
ROUND() - round the values in the column

Conditionals can also be added to aggregates.

SELECT aggregate(column_name) 
FROM table_name
WHERE conditional;

Round values

SELECT ROUND(column_name, int_decimal_places)
FROM table_name;

One can also stack aggregates, for example:

SELECT ROUND(AVG(table_name), int_decimal_places)
FROM table_name;

To calculate an aggregate for data with certain characteristics:
GROUP BY - Groups a result set based on an aggregate function (COUNT(), MIN(), MAX(), SUM(), AVG()). It lists the number in each group.

SELECT column_name, aggregate(other_column_name)
FROM table_name
GROUP BY column_name
ORDER BY column_name;

It is used with aggregate functions and with SELECT to arrange identical data into groups. It comes after WHERE statements and before ORDER BY or LIMIT.

Can also be used with a calculation done on a column.

SELECT aggregate(column_name), aggregate(other_column_name)
FROM table_name
GROUP BY int_num_of_column_selected
ORDER BY int_num_of_column_selected;

HAVING
To limit the results of a query based on values of the individual rows: WHERE.
To limit the results of a query based on an aggregate property: HAVING.
(After GROUP BY, but before ORDER BY and LIMIT)

SELECT column_name1, 
    column_name2, 
    COUNT(column_name3)
FROM table_name
GROUP BY 1, 2
HAVING COUNT(column_name) conditional;

SQLite function for returning formatted date

strftime(format, column)

Formats:
%Y: year (YYYY)
%m:month (01-12)
%d: day (1-31)
%H: 24-hour clock (00-23)
%M: minute (00-59)
%S: seconds (00-59)