|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;