MySQL Ordering and Math Functions

MySQL Ordering and Math Functions

In this example we have a small table that needs to be sorted in order of the title.

SELECT * FROM products;


+--------+-----------------+----------+-------+
| ProdID | title           |         quantity | price |
+--------+-----------------+----------+-------+
|      1 | Shoes           |                21   |  4.99 |
|      2 | Jackets         |                34 | 16.99 |
|      3 | Shampoo         |             45 |  1.99 |
|      4 | Windows Cleaner |        78 |  2.49 |
|      5 | Car Magazine    |           71 |  1.49 |
|      6 | Eggs            |                 177 |  1.42 |
|      7 | Tea bags        |               237 |  1.29 |
|      8 | Bread           |                12 |  0.99 |
+--------+-----------------+----------+-------+


The query below sorted the title column in to alphabetical order.

SELECT * FROM products ORDER BY products.title;



+--------+-----------------+----------
| ProdID | title           | quantity | price |
+--------+-----------------+----------
|      8 | Bread                   12 |  0.99 |
|      5 | Car Magazine       71 |  1.49 |
|      6 | Eggs                    177 |  1.42 |
|      2 | Jackets                 34 | 16.99 |
|      3 | Shampoo              45 |  1.99 |
|      1 | Shoes                   21 |  4.99 |
|      7 | Tea bags               237 |  1.29 |
|      4 | Windows Cleaner | 78 |  2.49 |
+--------+-----------------+----------

If the same query was on the price column it would sort numerically, for example the below query will sort the price column is a descending order from highest price to lowest.

SELECT * FROM products ORDER BY products.price DESC;



+--------+-----------------+----------
| ProdID | title           | quantity | price |
+--------+-----------------+----------
|      2 | Jackets                  34 | 16.99 |
|      1 | Shoes                    21 |  4.99 |
|      4 | Windows Cleaner   78 |  2.49 |
|      3 | Shampoo                45 |  1.99 |
|      5 | Car Magazine         71 |  1.49 |
|      6 | Eggs                      177 |  1.42 |
|      7 | Tea bags               237 |  1.29 |
|      8 | Bread                     12 |  0.99 |
+--------+-----------------+---------


Similarly you can use the ASC keyword instead of DESC if you need ascending order.


Now although this is a very small table and is obvious how many rows we have, but some tables may have tens of thousands of rows of data, if for some reason we need to find of the number of rows in the table we could use.

SELECT COUNT(*) FROM products;

8

Now we need to know the average cost of our products, we would use.

SELECT AVG (price) FROM products;


+------------+
| AVG(price) |
+------------+
|   3.956250  |
+------------+


Now for some reason we need to find the total sell price of all what we have in stock, we could add all the rows together or we could use.


SELECT SUM(price) FROM products;



+------------+
| SUM(price) |
+------------+
|      31.65     |
+------------+

From time to time you may need a random number generated, this can be done with the RAND function.

SELECT RAND();


+------------------------+
| RAND()                        |
+------------------------+
| 0.19249551668064252 |
+-------------------------+

The above query give a random number from 0 to 1, if we need a larger random number we would use.

SELECT RAND()*10;