[PostgreSQL] Aggregate Functions and Window Functions

Aggregate FunctionsWindow Functions
Aggregate functions compute a single result from a set of input values.

➞ it aggregates data from a set of rows into a single row.
Window functions provide the ability to perform calculations across sets of rows that are related to the current query row.

➞it does not reduce the number of rows returned by the query
Aggregate functions can also used as Window functions if follow the syntax of Window functions.There are built-in Window functions and these functions must be invoked using window function syntax (see below for details).

Window functions syntax:

window_function(arg1, arg2,..) OVER (PARTITION BY expression ORDER BY expression)

Aggregate functions used as Window functions example:

SELECT
 product_name,
 price,
 brand_name,
 AVG(price) OVER (PARTITION BY brand_name ORDER BY brand_name ASC)
FROM
 products;

Aggregate functions example:
In SELECT clause with multiple columns which not used by aggregate functions, these columns must use GROUP BY to group a set of rows which aggregate functions will perform calculation. Then using HAVING to filter the result set after run the GROUP BY (can only use with column used in aggregate functions).

SELECT
 brand_name,
 COUNT(product_name)
FROM
 products
GROUP BY
 brand_name
HAVING
 COUNT(product_name) > 10;

More about list of Aggregate functions: PostgreSQL 9.5
More about list of Window functions: PostgreSQL 9.5