Aggregating Data in SQL

CodingDatabases One can use the following operators to perform operations across an entire column/subset of a column (window):

• SUM - sum all values in a column/window
• MIN - return the minimum of all values in a column/window
• MAX - return the minimum of all values in a column/window
• AVG - averages all values in a column/window
• COUNT - counts all rows are in a column/window
• FIRST_VALUE() - returns the first value in the column/window
• LAST_VALUE() - returns the last value in the column/window

These operations can act on subsets of rows by three methods:

1. GROUP BY - operates on rows with the same values for particular columns
2. HAVING - filter aggregated data
3. OVER - specify that the aggregation will occur over a window which can be specified using a PARTITION BY column clause These operators are used in the SELECT clause of a query:

SELECT column1,
	   MIN(column1) AS min_col1,
	   MAX(column1) AS max_col1,
	   SUM(column1) OVER (
		   PARTITION BY column2
		   ORDER BY column1) AS window_sum,
FROM table
GROUP BY column1
HAVING min_col1 >= L AND max_col1 <= U;

One can also use COUNT on the data returned by a CASE statement used for conditionally filtering data:

SELECT 
	COUNT(CASE
		WHEN condition THEN 1
		ELSE NULL
	END) AS new_count
FROM table;