|
<< Click to Display Table of Contents >> SUM function |
  
|
|
<< Click to Display Table of Contents >> SUM function |
|
Calculates the sum of values for a column.
SUM([ALL] column_reference | DISTINCT column_reference)
Use SUM to calculate the total of a numeric value (column, literal, parameter, or caculated values) using a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the aggregation. NULL column values are not counted in the calculation. If the number of qualifying rows is zero, SUM returns a NULL value.
SELECT SUM(ItemsTotal)
FROM Orders
Constant values (such as literals and parameter values) can only be used with the SUM function if part of a calculation involving a table column. If SUM is applied to a calculation, the calculation is performed for each row and then the total calculated based on the data as modified by the calculation.
SELECT SUM(Salary + (Sarary * 0.1))
FROM "Employee.db"
ALL returns the smallest value for all rows. When DISTINCT is not specified, ALL is the implied default.
DISTINCT ignores duplicate values when calculating the smallest value in the specified column.
MIN returns the smallest value in a column or a calculation using a column performed for each row (a calculated field).
SELECT SUM(ItemsTotal), SUM(ItemsTotal * 0.0825) AS TotalTax
FROM Orders
When used with a GROUP BY clause, SUM returns one row for each group with a total of the column's value for that group (one subtotal for each group). The statement below aggregates the total value for the order totals column in the Orders table, producing a subtotal for each company in the Company table.
SELECT C."Company", SUM(O."ItemsTotal") AS SubTotal
FROM "Customer.db" C, "Orders.db" O
WHERE (C."CustNo" = O."CustNo")
GROUP BY C."Company"
ORDER BY C."Company"
SELECT statements. SUM operates only on numeric values. To use SUM on non-numeric values, first use the CAST function to convert the column to a numeric type.
