Mathematical operator symbols - Amazon Redshift
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 | +-----------+--------+----------+-----------+