Murach's SQL server for developers 2022: Chapter 5 terms

NOTE: These are not the exact definitions from the book!

scalar functions

Functions that operate on a single value (or a set of single values from one row) and return a single value, such as LEN, UPPER, ROUND, and GETDATE. They are applied to each row individually. Example: SELECT UPPER(VendorName), LEN(VendorName) FROM Vendors;

aggregate function

A function that operates on a set of rows and returns a single summary value, such as SUM, AVG, MIN, MAX, and COUNT. Most ignore NULL values. Example: SELECT AVG(InvoiceTotal) FROM Invoices;

column function

Another term for an aggregate function: a function that performs a calculation on the values in a column across multiple rows. Example: SELECT MAX(InvoiceTotal) FROM Invoices;

summary query

A query that uses aggregate functions (often with GROUP BY and HAVING) to return summarized data, such as totals or averages, rather than individual detail rows. Example: SELECT VendorID, SUM(InvoiceTotal) AS TotalDue FROM Invoices GROUP BY VendorID HAVING SUM(InvoiceTotal) > 1000;

scalar aggregate

A summary query that uses aggregate functions without a GROUP BY clause, returning a single row that summarizes the entire result set. Example: SELECT COUNT(*) AS InvoiceCount, SUM(InvoiceTotal) AS GrandTotal FROM Invoices; (returns one row)

vector aggregate

A summary query that uses aggregate functions with a GROUP BY clause, returning one summary row for each group. Example: SELECT VendorID, COUNT(*) AS InvoiceCount FROM Invoices GROUP BY VendorID; (one row per vendor)

cumulative total

A running total that adds each row's value to the sum of all previous rows in an ordered set, calculated with an aggregate window function such as SUM() OVER (ORDER BY …). Example: SELECT InvoiceDate, InvoiceTotal, SUM(InvoiceTotal) OVER (ORDER BY InvoiceDate ROWS UNBOUNDED PRECEDING) AS RunningTotal FROM Invoices; (Specifying ROWS avoids the default RANGE frame, which gives tied dates the same total.)

moving average

An average calculated over a sliding window of rows (for example, the current row and the two before it), defined in an OVER clause with a frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Example: SELECT InvoiceDate, InvoiceTotal, AVG(InvoiceTotal) OVER (ORDER BY InvoiceDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg FROM Invoices; (The first two rows average fewer than three values.)