Mathematical operator symbols - Amazon Redshift
Supported operatorsExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Mathematical operator symbols

The following table lists the supported mathematical operators.

Supported operators

Operator Description Example Result
+ addition 2 + 3 5
- subtraction 2 - 3 -1
* multiplication 2 * 3 6
/ division 4 / 2 2
% modulo 5 % 4 1
^ exponentiation 2.0 ^ 3.0 8
|/ square root | / 25.0 5
||/ cube root || / 27.0 3
@ absolute value @ -5.0 5
<< bitwise shift left 1 << 4 16
>> bitwise shift right 8 >> 2 2
& bitwise and 8 & 2 0

Examples

The following examples use the TICKIT sample database. For more information, see Sample database.

To calculate the commission paid plus a $2.00 handling for a given transaction, use the following example. 

SELECT
    commission,
    (commission + 2.00) AS comm
FROM
    sales
WHERE
    salesid = 10000;

+------------+-------+
| commission | comm  |
+------------+-------+
|      28.05 | 30.05 |
+------------+-------+

To calculate 20 percent of the sales price for a given transaction, use the following example. 

SELECT pricepaid, (pricepaid * .20) as twentypct 
FROM sales 
WHERE salesid=10000;

+-----------+-----------+
| pricepaid | twentypct |
+-----------+-----------+
|       187 |      37.4 |
+-----------+-----------+

To forecast ticket sales based on a continuous growth pattern, use the following example. In this example, the subquery returns the number of tickets sold in 2008. That result is multiplied exponentially by a continuous growth rate of 5% over 10 years. 

SELECT (SELECT SUM(qtysold) FROM sales, date
WHERE sales.dateid=date.dateid AND year=2008)^((5::float/100)*10) AS qty10years;

+------------------+
|    qty10years    |
+------------------+
| 587.664019657491 |
+------------------+

To find the total price paid and commission for sales with a date ID that is greater than or equal to 2000, use the following example. Then subtract the total commission from the total price paid. 

SELECT SUM(pricepaid) AS sum_price, dateid,
SUM(commission) AS sum_comm, (SUM(pricepaid) - SUM(commission)) AS value
FROM sales 
WHERE dateid >= 2000
GROUP BY dateid 
ORDER BY dateid 
LIMIT 10;

+-----------+--------+----------+-----------+
| sum_price | dateid | sum_comm |   value   |
+-----------+--------+----------+-----------+
|    305885 |   2000 | 45882.75 | 260002.25 |
|    316037 |   2001 | 47405.55 | 268631.45 |
|    358571 |   2002 | 53785.65 | 304785.35 |
|    366033 |   2003 | 54904.95 | 311128.05 |
|    307592 |   2004 |  46138.8 |  261453.2 |
|    333484 |   2005 |  50022.6 |  283461.4 |
|    317670 |   2006 |  47650.5 |  270019.5 |
|    351031 |   2007 | 52654.65 | 298376.35 |
|    313359 |   2008 | 47003.85 | 266355.15 |
|    323675 |   2009 | 48551.25 | 275123.75 |
+-----------+--------+----------+-----------+