SQL provides powerful ways to manipulate numerical data within queries. This tutorial covers how to combine values from multiple columns within a row, and how to calculate aggregated sums across multiple rows.
Combining Values Within a Row
Often, you’ll need to create a new value based on the combination of values from different columns within the same row. This is straightforward using simple arithmetic operators.
For example, suppose you have a table named products with columns price and shipping_cost. To calculate the total cost for each product, you can use the following query:
SELECT
product_name,
price + shipping_cost AS total_cost
FROM
products;
This query selects the product_name and calculates a new column named total_cost by adding the values from the price and shipping_cost columns for each row.
Aggregating Values Across Rows with SUM()
The SUM() function is an aggregate function that calculates the total sum of values in a column. It’s particularly useful for calculating totals, averages, and other summary statistics.
To use SUM(), you typically combine it with the GROUP BY clause. The GROUP BY clause groups rows based on one or more columns, allowing you to calculate aggregate functions (like SUM()) for each group.
Let’s illustrate with an example. Suppose you have a table named orders with columns customer_id and order_amount. To calculate the total order amount for each customer, you can use the following query:
SELECT
customer_id,
SUM(order_amount) AS total_order_amount
FROM
orders
GROUP BY
customer_id;
This query groups the rows by customer_id and then calculates the SUM() of the order_amount for each customer group, creating a new column called total_order_amount.
Combining Row-Wise Calculations and Aggregation
You can also combine both techniques. For example, if you have a table items with columns category and value, and you want to calculate the total value for each category, you might use:
SELECT
category,
SUM(value) AS total_value
FROM
items
GROUP BY
category;
This query calculates the total value per category.
Handling NULL Values
A common issue when working with numerical data is the presence of NULL values. If any of the values being added or summed are NULL, the result will typically be NULL. To avoid this, you can use the COALESCE() function.
COALESCE() takes one or more arguments and returns the first non-NULL argument. This is particularly useful for replacing NULL values with a default value (e.g., 0).
For instance, to handle potential NULL values in the price and shipping_cost columns when calculating total_cost, you can use:
SELECT
product_name,
COALESCE(price, 0) + COALESCE(shipping_cost, 0) AS total_cost
FROM
products;
This query replaces any NULL values in price or shipping_cost with 0 before performing the addition, ensuring that the total_cost is calculated correctly even if some values are missing. This prevents the entire total_cost from becoming NULL.