SQL Aggregate and Scalar Functions

SQL aggregate functions return a single value, calculated from values in a column. Useful aggregate functions: AVG() - Returns the average valueSELECT AVG(column_name) FROM table_name; COUNT() - Returns the number of rowsSELECT COUNT(column_name) FROM table_name;SELECT COUNT(DISTINCT column_name) FROM table_name; FIRST() - Returns the first valueMYSQL: SELECT column_name FROM table_name ORDER BY column_name ASC LIMIT 1;ORACLE: SELECT column_name FROM table_nameWHERE ROWNUM <=1 ORDER BY column_name ASC;SQLSERVER: SELECT TOP 1 column_name FROM table_nameORDER BY column_name ASC; LAST() - Returns the last valuemesma sintaxe que FIRST(), mas usando DESC MAX() - Returns the largest valueSELECT MAX(column_name) FROM table_name; MIN() - Returns the smallest valueSELECT MIN(column_name) FROM table_name; SUM() - Returns the sumSELECT SUM(column_name) FROM table_name; GROUP BY - Used with aggregate functionsSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name; HAVING - Filter used with aggregate functionsSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value;

SQL Scalar functions SQL scalar functions return a single value, based on the input value.Useful scalar functions: UCASE() - Converts a field to upper case LCASE() - Converts a field to lower case MID() - Extract characters from a text field LEN() - Returns the length of a text field ROUND() - Rounds a numeric field to the number of decimals specified NOW() - Returns the current system date and time FORMAT() - Formats how a field is to be displayed