Calculations, functions and CASE

Lesson 6 of 21 · Part 1 · Querying data · about 15 minutes

After SELECT you can write more than column names: you can write a calculation. What is each product's stock worth? Price times stock:

main.sql

1SELECT name, price, stock, price * stock2FROM products;

Output

name	price	stock	price * stock
Notebook	4.50	120	540.00
Pens (10)	6.00	80	480.00
Backpack	39.90	15	598.50
Water bottle	12.00	40	480.00
Headphones	59.00	10	590.00
Desk lamp	25.00	0	0.00
USB cable	8.50	200	1700.00
Coffee mug	9.90	60	594.00
Phone case	15.00	35	525.00
Tote bag	11.00	25	275.00

10 rows

The result has an extra column, worked out for every row from that row's own price and stock. The data in the table doesn't change; this column exists only in the result.

Math on columns

Add, subtract, multiply and divide are +, -, * and /; multiplying and dividing go before adding and subtracting, and parentheses go first. Prices after 20% off:

main.sql

1SELECT name, price, price * 0.8, price / 32FROM products3WHERE category = 'Stationery';

Output

name	price	price * 0.8	price / 3
Notebook	4.50	3.600	1.500000
Pens (10)	6.00	4.800	2.000000

2 rows

The prices had two decimal places, and times 0.8 they have three (3.600). / gives a result with decimals, and MySQL adds 4 more places by default, so 4.50 divided by 3 shows as 1.500000; in other databases, dividing two whole numbers may give only the whole part.

AS: naming a column

A calculated column is named after its calculation, which is long and ugly. Give it a name with AS; this is called an alias:

main.sql

1SELECT name, price * stock AS stock_value2FROM products3ORDER BY stock_value DESC, id;

Output

name	stock_value
USB cable	1700.00
Backpack	598.50
Coffee mug	594.00
Headphones	590.00
Notebook	540.00
Phone case	525.00
Pens (10)	480.00
Water bottle	480.00
Tote bag	275.00
Desk lamp	0.00

10 rows

ORDER BY can use the alias. The pens and the water bottle are both worth 480.00, so sorting by id as well fixes their order. By habit, aliases are lowercase, with underscores between words and no spaces.

WHERE, though, can't use an alias:

main.sql

1SELECT name, price * stock AS stock_value2FROM products3WHERE stock_value > 590;

Output

ERROR 1054 (42S22): Unknown column 'stock_value' in 'where clause'

WHERE runs before SELECT, when there is no stock_value column yet, so the database says it doesn't know it. Write the calculation again in WHERE: WHERE price * stock > 590. Lesson 10 explains the order in which the parts of a query run.

ROUND

ROUND(number, places) rounds to that many decimal places; without the places, it rounds to a whole number:

main.sql

1SELECT name, price, ROUND(price), ROUND(price * 0.85, 2) AS sale_price2FROM products3WHERE price > 20;

Output

name	price	ROUND(price)	sale_price
Backpack	39.90	40	33.92
Headphones	59.00	59	50.15
Desk lamp	25.00	25	21.25

3 rows

Things like ROUND are called functions: a name followed by parentheses, with the values to work on inside, separated by commas.

Text functions

A few common ones for text: CONCAT joins pieces of text together, UPPER turns letters into capitals, and LENGTH gives the length:

main.sql

1SELECT CONCAT(name, ' (', city, ')') AS who, UPPER(email) AS email2FROM customers3WHERE id <= 3;

Output

who	email
Ana Silva (London)	[email protected]
Ben Okafor (Paris)	[email protected]
Chloe Martin (London)	NULL

3 rows

Chloe has no email, and UPPER(NULL) is still NULL. CONCAT is the same: join a NULL on and the whole result is NULL. Other databases often join text with || (name || city), but in MySQL || means "or" by default, so use CONCAT here.

Careful with lengths: LENGTH counts bytes, and CHAR_LENGTH counts characters. For plain English letters they agree, but an accented letter like é takes 2 bytes, and a Chinese character takes 3:

main.sql

1SELECT 'cafe' AS word, LENGTH('cafe') AS bytes, CHAR_LENGTH('cafe') AS chars;2SELECT 'café' AS word, LENGTH('café') AS bytes, CHAR_LENGTH('café') AS chars;3SELECT '咖啡' AS word, LENGTH('咖啡') AS bytes, CHAR_LENGTH('咖啡') AS chars;

Output

Statement on line 1

word	bytes	chars
cafe	4	4

1 row

Statement on line 2

word	bytes	chars
café	5	4

1 row

Statement on line 3

word	bytes	chars
咖啡	6	2

1 row

CASE: a different value for each condition

CASE is a chain of "if this, then that": it looks at each WHEN from the top, and the first one that is true gives the value after its THEN; if none is true, it gives the ELSE value. It closes with END. A stock label for each product:

main.sql

1SELECT name, stock,2    CASE3        WHEN stock = 0 THEN 'sold out'4        WHEN stock < 20 THEN 'running low'5        ELSE 'in stock'6    END AS stock_status7FROM products;

Output

name	stock	stock_status
Notebook	120	in stock
Pens (10)	80	in stock
Backpack	15	running low
Water bottle	40	in stock
Headphones	10	running low
Desk lamp	0	sold out
USB cable	200	in stock
Coffee mug	60	in stock
Phone case	35	in stock
Tote bag	25	in stock

10 rows

Guess first

What is the label of the 15.00 phone case?

1SELECT name,2    CASE3        WHEN price < 50 THEN 'cheap'4        WHEN price < 20 THEN 'very cheap'5        ELSE 'pricey'6    END AS label7FROM products8WHERE price = 15;

CASE uses the first WHEN that is true. 15 is less than 50, so the first one is already true and WHEN price < 20 is never looked at. Write the conditions from narrowest to widest: < 20 first, then < 50.

Don't forget the final END: without it, MySQL reaches AS still waiting for END and gives a syntax error, ERROR 1064.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

Exercise 1

What the stock is worth

For the electronics, get two columns: name and the stock's total value, stock_value (price times stock), sorted by id.

It should print

name	stock_value
Headphones	590.00
Desk lamp	0.00
USB cable	1700.00
Phone case	525.00

4 rows

exercise1.sql

1SELECT name, price, stock2FROM products3WHERE category = 'Electronics';

Output

Press Run or Ctrl+Enter

Exercise 2

15% off

The bags are 15% off. Show three columns, name, price and sale_price, with sale_price rounded to 2 decimal places, sorted by id.

It should print

name	price	sale_price
Backpack	39.90	33.92
Tote bag	11.00	9.35

2 rows

exercise2.sql

1SELECT name, price2FROM products3WHERE category = 'Bags';

Output

Press Run or Ctrl+Enter

Exercise 3

Price levels

Label every product with three columns, name, price and price_level: under 10 is cheap, 10 up to (but not including) 30 is medium, and the rest is pricey, sorted by id.

It should print

name	price	price_level
Notebook	4.50	cheap
Pens (10)	6.00	cheap
Backpack	39.90	pricey
Water bottle	12.00	medium
Headphones	59.00	pricey
Desk lamp	25.00	medium
USB cable	8.50	cheap
Coffee mug	9.90	cheap
Phone case	15.00	medium
Tote bag	11.00	medium

10 rows

exercise3.sql

1SELECT name, price2FROM products;

Output

Press Run or Ctrl+Enter

What you learned

SELECT can hold calculations, worked out for every row; the table's data doesn't change.
AS name gives a column an alias; ORDER BY can use it, WHERE can't.
ROUND(number, places) rounds; CONCAT joins text, UPPER makes capitals, LENGTH counts bytes and CHAR_LENGTH characters.
When a function meets NULL, the result is usually NULL too.
CASE WHEN … THEN … ELSE … END uses the first WHEN that is true; don't forget the END.