We'd like to have a store wide sale at our Clown Store. Everything is 25% off! All this clown shit gotta go!

Our requirements dictate that we need to show product id, product description, product price, the marked down amount, the price after mark down, and because we might get a number with more than two decimal places, we need to format the output so that it looks like currency on the screen. We're going to sort from cheapest to most expensive.

Let's build our query:

select 
	id, -- the product id
	description, -- the description of the product
	price as originalPrice, -- the original price
	(price * 0.25) as markDown, -- the amount that we'll mark the price down
	( price - price * 0.25 ) as realTotal, -- the calculated final mark down price
	format ( price - price * 0.25, 2) as roundedTotal -- a formatted version of the above
from 
	products 
order by 
	price;

A one liner with output:

mysql> select id, description, price as originalPrice, (price * 0.25) as markDown, ( price - price * 0.25 ) as realTotal, format ( price - price * 0.25, 2) as roundedTotal from products order by price;
+----+-------------------------------------------+---------------+----------+-----------+--------------+
| id | description                               | originalPrice | markDown | realTotal | roundedTotal |
+----+-------------------------------------------+---------------+----------+-----------+--------------+
|  7 | Hot Slippery Lube                         |          2.56 |   0.6400 |    1.9200 | 1.92         |
|  8 | Rainbow Clown Suit                        |          5.95 |   1.4875 |    4.4625 | 4.46         |
|  9 | Clown Wig                                 |          7.95 |   1.9875 |    5.9625 | 5.96         |
|  5 | Blow Up Balloon Animal Balloons           |          8.95 |   2.2375 |    6.7125 | 6.71         |
|  6 | Hand Cuffs                                |         10.88 |   2.7200 |    8.1600 | 8.16         |
|  1 | Large Floppy Toothbrush With Suds         |         19.95 |   4.9875 |   14.9625 | 14.96        |
|  2 | Large Floppy Clown Shoes                  |         19.95 |   4.9875 |   14.9625 | 14.96        |
|  3 | Vibra-Glove Trick Buzzin Glove            |         29.95 |   7.4875 |   22.4625 | 22.46        |
|  4 | Sense-attentive Electrocute Your Friends! |         49.95 |  12.4875 |   37.4625 | 37.46        |
+----+-------------------------------------------+---------------+----------+-----------+--------------+
9 rows in set (0.00 sec)

This query is interesting because we're using the field, price, multiple times and creating new columns with calculated results for each new column. The math that you can do here can go deep. For example, there are the standard trigonometric functions, sin(), cos() available, and many more.

Your formulas can span multiple lines and can be as complex as you need them to be as well. For example, grok this query:

select 
	( 
		6371 * acos(
			cos( radians(some_latitude) ) *
			cos( radians( lat ) ) *
			cos( radians( lng ) - radians(some_longitude) ) +
			sin( radians(some_latitude) ) *
			sin( radians(lat)	)
		)
	) as distance 
from
	locations
having
	distance < 25
order by 
	distance;

This query isn't compatible with our database. It's here to serve as an example to show that you can have a large calculation which returns one column.

This very slow query calculates how close one latitude/longitude is to another in a table.

Here's a link to an article that talks about geo location database queries.