Aggregate Functions

Used to return a result calculated from the values in a specified field

 

A few of the more common functions:

  • sum()
  • avg()
  • min()
  • max()
  • count()

*Note: Thousands of functions can be found here: http://dev.mysql.com/doc/refman/5.6/en/func-op-summary-ref.html

 

 

Consider the orders table:

mysql> select * from orders;
+---------+--------+----------+-----+------+
| OrderID | CustID | Part     | Qty | Cost |
+---------+--------+----------+-----+------+
|       1 |      3 | CPU      |   2 |  975 |
|       2 |      1 | RAM      |   3 |  433 |
|       3 |      2 | SSD      |   2 |  768 |
|       4 |      3 | Monitor  |   3 |  250 |
|       5 |      3 | MoBo     |   4 |  666 |
|       6 |      1 | Case     |   2 |  150 |
|       7 |      2 | HDD      |  10 |  325 |
|       8 |      3 | Keyboard |   5 |  250 |
+---------+--------+----------+-----+------+
8 rows in set (0.00 sec)

 

 

Use sum() on the Qty field to find out how many items have been sold:

mysql> select sum(Qty) from orders;
+----------+
| sum(Qty) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

 

 

Use avg() on the Cost field to get the average cost of an item:

mysql> select avg(Cost) from orders;
+-----------+
| avg(Cost) |
+-----------+
|  452.1250 |
+-----------+
1 row in set (0.00 sec)

 

 

Use min() to find the cost of the cheapest item:

mysql> select min(Cost) from orders;
+-----------+
| min(Cost) |
+-----------+
|        50 |
+-----------+
1 row in set (0.00 sec)

 

Use max() to find the cost of the most expensive item:

mysql> select max(Cost) from orders;
+-----------+
| min(Cost) |
+-----------+
|       975 |
+-----------+
1 row in set (0.00 sec)

 

Use count() to find out how many orders have been placed by a specific customer:

mysql> select count(Qty) from orders where CustID =3;
+------------+
| count(Qty) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

Leave a Reply