Aggregate Functions | Window 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