How to Subtract in SQL

Subtracting Numeric Values

Subtracting numeric values in SQL is straightforward. The - operator is used for subtraction, similar to how it’s done in other programming languages.

Subtracting Columns in a Table

Suppose you have a table named sales that stores the actual and projected sales figures:

CREATE TABLE sales (
    id INT PRIMARY KEY,
    actual_sales DECIMAL(10, 2),
    projected_sales DECIMAL(10, 2)
);

INSERT INTO sales (id, actual_sales, projected_sales)
VALUES
(1, 1000.00, 1200.00),
(2, 1500.00, 1500.00),
(3, 1300.00, 1250.00);

To calculate the difference between actual sales and projected sales, you can write the following query:

SELECT 
    id,
    actual_sales,
    projected_sales,
    actual_sales - projected_sales AS difference
FROM 
    sales;

Expected Output:

+----+--------------+-----------------+------------+
| id | actual_sales | projected_sales | difference |
+----+--------------+-----------------+------------+
|  1 |      1000.00 |         1200.00 |    -200.00 |
|  2 |      1500.00 |         1500.00 |       0.00 |
|  3 |      1300.00 |         1250.00 |      50.00 |
+----+--------------+-----------------+------------+
3 rows in set (0.00 sec)

Subtracting Dates in SQL

SQL also allows you to subtract dates to find the difference in terms of days, months, or years, depending on the database system you’re using.

Subtracting Dates in MySQL

Consider a table employees with start_date and end_date columns:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    start_date DATE,
    end_date DATE
);

INSERT INTO employees (id, start_date, end_date)
VALUES
(1, '2023-01-01', '2023-08-01'),
(2, '2022-05-15', '2023-05-15'),
(3, '2023-03-01', '2023-03-31');

To calculate the difference in days between end_date and start_date, you can use the DATEDIFF function:

SELECT 
    id,
    start_date,
    end_date,
    DATEDIFF(end_date, start_date) AS days_difference
FROM 
    employees;

Expected Output:

+----+------------+------------+-----------------+
| id | start_date | end_date   | days_difference |
+----+------------+------------+-----------------+
|  1 | 2023-01-01 | 2023-08-01 |             212 |
|  2 | 2022-05-15 | 2023-05-15 |             365 |
|  3 | 2023-03-01 | 2023-03-31 |              30 |
+----+------------+------------+-----------------+
3 rows in set (0.01 sec)

Subtracting Timestamps

Subtracting timestamps is useful when you need to calculate time differences in seconds, minutes, hours, etc.

Subtracting Timestamps in PostgreSQL

Assume you have a logs table that stores timestamps for when an event started and ended:

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP,
    end_time TIMESTAMP
);

INSERT INTO logs (start_time, end_time)
VALUES
('2023-08-01 10:00:00', '2023-08-01 12:00:00'),
('2023-08-02 09:15:00', '2023-08-02 10:45:00');

To calculate the difference in hours between end_time and start_time, you can use the EXTRACT function:

SELECT 
    id,
    start_time,
    end_time,
    EXTRACT(EPOCH FROM end_time - start_time) / 3600 AS hours_difference
FROM 
    logs;

Expected Output:

id |     start_time      |      end_time       |  hours_difference
----+---------------------+---------------------+--------------------
  1 | 2023-08-01 10:00:00 | 2023-08-01 12:00:00 | 2.00
  2 | 2023-08-02 09:15:00 | 2023-08-02 10:45:00 | 1.50
(2 rows)

Time: 9.567 ms

Subtracting Across Rows

In some cases, you may want to subtract values across different rows. This is commonly done using window functions.

Let’s use a table monthly_sales to calculate the difference in sales from one month to the next:

CREATE TABLE monthly_sales (
    month DATE PRIMARY KEY,
    total_sales DECIMAL(10, 2)
);

INSERT INTO monthly_sales (month, total_sales)
VALUES
('2023-01-01', 2000.00),
('2023-02-01', 2200.00),
('2023-03-01', 2100.00);

To calculate the month-over-month difference in sales, you can use the LAG function:

SELECT 
    month,
    total_sales,
    total_sales - LAG(total_sales) OVER (ORDER BY month) AS sales_difference
FROM 
    monthly_sales;

Expected Output:

   month    | total_sales | sales_difference
------------+-------------+------------------
 2023-01-01 |     2000.00 |           [NULL]
 2023-02-01 |     2200.00 |           200.00
 2023-03-01 |     2100.00 |          -100.00
(3 rows)

Time: 5.648 ms

Conclusion

Subtraction in SQL is a versatile operation that you can apply to numbers, dates, timestamps, and even across rows. Despite differences in syntax across SQL databases, the fundamental operation is the same. The key lies in familiarizing yourself with these nuances.

Other Articles you may enjoy:

• How to Use SQL Query Templates
• How to Rename a Column Using ALTER TABLE in SQL