SQL functions and GROUP BY

SQL has many built-in functions.

Single-row functions give one result for each row:

• Math: POWER(x, y) raises x to the power y; ROUND(n, d) rounds n to d decimal places; MOD(a, b) gives the remainder of a ÷ b.
• String: UCASE/LCASE change case; MID(s, start, length) or SUBSTR take part of a string; LENGTH counts characters; LEFT and RIGHT take characters from either end; INSTR finds where a substring starts; LTRIM, RTRIM and TRIM remove extra spaces.
• Date: NOW() gives the current date and time; DATE(), MONTH(), MONTHNAME(), YEAR(), DAY() and DAYNAME() extract parts of a date.

Aggregate (multiple-row) functions give one result for a whole group of rows: MAX, MIN, AVG, SUM and COUNT.

COUNT has three forms:

• COUNT(*) counts all rows.
• COUNT(column) counts the values in that column, ignoring NULLs.
• COUNT(DISTINCT column) counts the different values.

GROUP BY column collects rows with the same value into groups, so that aggregate functions can be applied to each group. HAVING puts a condition on the groups. For example, to find customers who bought more than one car: group the SALE table by CustID, and keep only groups with HAVING COUNT(*) > 1.

Remember the difference: WHERE filters rows (before grouping); HAVING filters groups (after grouping).