SQL arithmetic and math

Link: SQL

General arithmetic operator §

+, -, *, /, ^ §

Work as usual, follow the order of standard arithmetic

Modulus % §

• Modulus or remainder function: returns the remainder of a division

SELECT 23 % 6; -- returns 5

• Useful to identify odd or even numbers

SELECT * FROM measurements
WHERE measurement_num % 2 = 1 -- find odd numbers

Math functions §

ABS() absolute value §

ABS()

SELECT ABS(open-close) AS absolute_diff
FROM stock_prices

ROUND(), CEIL() and FLOOR() §

• ROUND(num, 2)

SELECT ROUND(AVG(close), 2) AS rounded_avg
FROM stock_prices

• CEIL(): round up
• FLOOR(): round down

POWER() squared values §

• POWER(num, x)~: num
• POWER(num, 2): same as ^

MOD() and % §

MOD(num, x): alternatively we can use %

-- same result
SELECT MOD(close, 5) AS remainder_mod,
       close%5 AS remainder_mod_alt

Division §

• For whole number like 10/4, SQL only keeps the integer part and discards the remainder

Query	SQL	Excel
SELECT 10/4	2	2.5
SELECT 10/2	5	5
SELECT 10/6	1	1.666667
SELECT 10.0/4	2.500000	2.5
SELECT 10/3.0	3.333333	3.333333

Solutions §

To show decimal output, there are few different ways

Specify decimals/floats using CAST() §

Query	SQL
CAST(10 AS DECIMAL)/4	2.500000
CAST(10 AS FLOAT)/4	2.5
10/CAST(6 AS DECIMAL)	1.666667
10/CAST(6 AS FLOAT)	1.666667

Multiply by 1.0 §

Use 1.0 to convert and format to a decimal data type.

Query	SQL
10/6	1
10*1.0/6	1.666667
10/6*1.0	1.0
10/(6*1.0)	1.666667

Use :: to divide §

Query	SQL
10::DECIMAL/4	2.5000000
10::FLOAT/4	2.5
10/4::DECIMAL	1.5000000
10/4::FLOAT	2.5
10::FLOAT/6	1.666667

Show Percentage §

• Use (num / den)* 100 to convert to percentage
• Futhurmore, use ROUND(num/den * 100, n) to round percentage
• Limit this form to visuals or reports only

Query	SQL
SELECT (actual_sales/target_sales) * 100	50.000000
SELECT ROUND((actual_sales/target_sales) * 100, 2)	50.00

SELECT advertiser_id, ROUND(revenue/spend * 100, 2) as roas
FROM ad_campaigns
GROUP BY advertiser_id
ORDER BY advertiser_id