How to Do Mathematical Operations in SQL?

When working with Microsoft SQL Server, you’ll quickly find that it’s not just about storing and retrieving data. You also have to learn how to do mathematical operations in SQL, in order to perform calculations directly in your queries. Many beginners ask: can I do calculations in SQL? The answer is YES, and SQL Server comes with built-in mathematical functions like ROUND(), FLOOR(), CEILING(), and many more.

In this post, I’ll show you how to use mathematical operations in SQL with practical examples, and you’ll see just how flexible SQL Server can be.

How to Do Mathematical Operations in SQL?

SQL Server lets you add, subtract, multiply, and divide numbers directly in your queries.

Example:

Let’s use our Products table to see the total price of items on stock

SELECT
	ProductID
	,UnitPrice
	,UnitsInStock
	,UnitPrice * UnitsInStock AS TotalPrice
FROM Products

Here, we’re multiplying Price and Quantity to calculate TotalPrice. This is one of the simplest ways to see how to use mathematical operations in SQL.

Here is what the results look like:

Rounding Numbers with ROUND

The ROUND() function is used when you want to round a number to a certain number of decimal places. Let’s say we want to round the Freight field from Orders table to only one decimal:

SELECT 
	OrderID
	,Freight
	,ROUND(Freight, 1) AS FreightRound
	,CAST(ROUND(Freight, 1) AS DECIMAL(10,1)) AS FreightCastRound
FROM Orders

When you use the ROUND function in SQL Server, it changes the precision of the calculation but not necessarily how the result is displayed. This often confuses beginners, because ROUND doesn’t control the number of decimals you see. It only controls the rounding logic.

Here is how it looks in our case:

If you look at the results, you’ll notice that FreightRound shows two decimals, while FreightCastRound shows only one. At first, this looks strange because both expressions are rounding to one decimal place. The difference comes from how SQL Server handles data types.

ROUND(Freight, 1) performs the rounding but keeps the original data type of the Freight column. If Freight is stored as decimal, SQL Server will still display two decimals, even though the rounding was done.

On the other hand, CAST(ROUND(Freight, 1) AS DECIMAL(10,1)) not only rounds the number but also forces SQL Server to store and display it with exactly one decimal place. In other words, ROUND affects the math, while CAST (or CONVERT) affects how many decimals are visible in the result.

Using FLOOR

The FLOOR() function always rounds a number down to the nearest whole number. Let’s user our Orders table for example:

SELECT 
	OrderID
	,Freight
	,FLOOR(Freight) AS FreightFloor
FROM Orders

Do you see the difference between Freight and FreightFloor?

Using CEILING

The CEILING() function does the opposite of FLOOR(). It rounds a number up to the nearest whole number.

SELECT 
	OrderID
	,Freight
	,CEILING(Freight) AS FreightCeiling
FROM Orders

Combining Functions

You can use these functions together to format or adjust your results. For example:

SELECT 
	OrderID
	,FLOOR(Freight) AS FreightFloor
	,CEILING(Freight) AS FreightCeiling
FROM Orders

Why Use Mathematical Functions in SQL?

• They save time by letting you calculate values on the fly.
• They reduce the need to do extra calculations in your application code.
• They make your queries more powerful, especially when working with totals, averages, and financial data.

Final Thoughts

So, now you know how to do mathematical operations in SQL using built-in functions like ROUND(), FLOOR(), and CEILING(). We also answered the common beginner’s question: can I do calculations in SQL? The answer is YES, and it’s often easier than you might think.

With these functions, you can format your results neatly, round numbers for reports, and handle real-world business logic right inside your queries. If you’ve ever wondered how to use mathematical operations in SQL, these examples should give you the confidence to start experimenting.

If you are interested in other mathematical operations in SQL, you can check one of my previous blog posts on Aggregate Functions in SQL, or you can visit this link.

If you would like to learn more about programming SQL queries, make sure you buy this book: T-SQL Fundamentals (Developer Reference)