SUM()
In this page:
Calculating Totals
The SUM() function adds up all the values in a numeric column across the matched rows. It is ideal for calculating total financial amounts or item quantities, like the total revenue from all orders.
Example: Calculating Totals
CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 100), (2, 250);
SELECT SUM(total) AS total_revenue FROM orders;Summing with Filters
You can restrict your addition to a subset of data before it's summed. Simply add a WHERE clause to sum only the rows that match your filter, such as summing only orders from a specific region.
Example: Summing with Filters
CREATE TABLE orders (id INT, region TEXT, total INT);
INSERT INTO orders VALUES (1, 'North', 100), (2, 'South', 250);
SELECT SUM(total) AS north_total FROM orders WHERE region = 'North';Handling NULL Values in SUM()
The SUM() function automatically ignores NULL values rather than treating them as zero or breaking the calculation. If a row contains a NULL, it is skipped without affecting the calculation at all.
Example: Handling NULL Values in SUM()
CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 100), (2, NULL), (3, 50);
SELECT SUM(total) AS total_ignoring_null FROM orders;Summing Expressions
You can perform arithmetic operations inside the SUM() function itself. The math is calculated for each row before the final total is summed, such as SUM(price * quantity) to get a true order total.
Example: Summing Expressions
CREATE TABLE order_items (id INT, price INT, quantity INT);
INSERT INTO order_items VALUES (1, 10, 3), (2, 20, 2);
SELECT SUM(price * quantity) AS order_total FROM order_items;SUM() with GROUP BY
You can combine SUM() with a GROUP BY clause to break a total down by category. This lets you calculate totals for distinct categories in your dataset, like revenue per product line instead of one company-wide figure.
Example: SUM() with GROUP BY
CREATE TABLE orders (id INT, product_line TEXT, total INT);
INSERT INTO orders VALUES (1, 'Books', 50), (2, 'Toys', 30), (3, 'Books', 20);
SELECT product_line, SUM(total) AS line_total FROM orders GROUP BY product_line;Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: