Functions
There are a number of built-in functions that return a result on a numeric column
| Function | Description |
|---|---|
| COUNT() | Returns the number of rows that match the specified criteria |
| AVG() | Returns the average value of a numeric column |
| SUM() | Returns the total sum of a numeric column |
| MIN() | Returns the smallest value of a selected column |
| MAX() | Returns the largest value of a selected column |
Sample Data
This is the table we will use as an example:
SELECT * FROM ar_invoices
| invoice# | customer# | salesperson# | invoice_date | invoice_amount | amount_paid | current_month_interest_charges | total_interest_charges |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 2019-06-17 10:43:53.727 | 100.00 | 1.00 | 0.00 | 0.00 |
| 2 | 2 | 2 | 2019-05-28 10:43:53.727 | 50.00 | 0.00 | 5.00 | 10.00 |
| 3 | 3 | 2 | 2019-06-17 10:43:53.727 | 70.00 | 0.00 | 10.00 | 20.00 |
| 4 | 2 | 1 | 2019-07-07 10:43:53.727 | 80.00 | 0.00 | 0.00 | 0.00 |
COUNT()
Returns the number of rows that match the specified criteria
Does not count NULL values
Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition
SELECT COUNT(salesperson#) AS [Number of Salespeople] FROM ar_invoices
| Number of Salespeople |
|---|
| 4 |
Notice how it counts every row for salesperson#
Combine COUNT() with DISTINCT to get unique values:
SELECT COUNT(DISTINCT salesperson#) AS [Number of Salespeople] FROM ar_invoices
| Number of Salespeople |
|---|
| 2 |
Use COUNT(*) with a WHERE clause to quckly filter what you want:
SELECT COUNT(DISTINCT salesperson#) AS [Number of Salespeople] FROM ar_invoices
| Number of Salespeople |
|---|
| 2 |
AVG()
Returns the average value of a numeric column
Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition
SELECT AVG(invoice_amount) AS [Average Invoice Amount] FROM ar_invoices
| Average Invoice Amount |
|---|
| 75.000000 |
SUM()
Returns the total sum of a numeric column
Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition
SELECT SUM(invoice_amount) AS [Sum of All Invoices] FROM ar_invoices
| Sum of All Invoices |
|---|
| 300.00 |
MIN()
Returns the smallest value of a selected column
Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition
SELECT MIN(invoice_amount) AS [Smallest Value of All Invoices] FROM ar_invoices
| Smallest Value of All Invoices |
|---|
| 50.00 |
MAX()
Returns the largest value of a selected column
Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition
SELECT MAX(invoice_amount) AS [Largest Value of All Invoices] FROM ar_invoices
| Largest Value of All Invoices |
|---|
| 100.00 |