Returns the minimum value from a group of values: MIN()

      
mysql>
mysql> CREATE TABLE Classes
    -> (
    ->     ClassID SMALLINT NOT NULL PRIMARY KEY,
    ->     Dept CHAR(4) NOT NULL,
    ->     Level ENUM('Upper', 'Lower') NOT NULL,
    ->     TotalStudents TINYINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Classes VALUES
    -> (1001, 'ANTH', 'Upper', 25),
    -> (1002, 'ANTH', 'Upper', 25),
    -> (1003, 'MATH', 'Upper', 18),
    -> (1004, 'ANTH', 'Lower', 19),
    -> (1005, 'ENGL', 'Upper', 28),
    -> (1006, 'MATH', 'Lower', 23),
    -> (1007, 'ENGL', 'Upper', 25),
    -> (1008, 'MATH', 'Lower', 29),
    -> (1009, 'ANTH', 'Upper', 25),
    -> (1010, 'ANTH', 'Lower', 30),
    -> (1011, 'ENGL', 'Lower', 26),
    -> (1012, 'MATH', 'Lower', 22),
    -> (1013, 'ANTH', 'Upper', 27),
    -> (1014, 'ANTH', 'Upper', 21),
    -> (1015, 'ENGL', 'Lower', 25),
    -> (1016, 'ENGL', 'Upper', 32);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT Dept, Level, MIN(TotalStudents) AS Minimum
    -> FROM Classes
    -> GROUP BY Dept, Level WITH ROLLUP;
+------+-------+---------+
| Dept | Level | Minimum |
+------+-------+---------+
| ANTH | Upper |      21 |
| ANTH | Lower |      19 |
| ANTH | NULL  |      19 |
| ENGL | Upper |      25 |
| ENGL | Lower |      25 |
| ENGL | NULL  |      25 |
| MATH | Upper |      18 |
| MATH | Lower |      22 |
| MATH | NULL  |      18 |
| NULL | NULL  |      18 |
+------+-------+---------+
10 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table Classes;
Query OK, 0 rows affected (0.00 sec)

mysql>

Related examples in the same category

1.	And to find the lowest commission that any of the sales staff is earning, use this:
2.	Use MIN( ) or MAX( ) With a GROUP BY clause
3.	MIN( ) and MAX( ) also work with expressions or values that are derived from column values.
4.	Do calculation with aggregate function
5.	Controlling String Case Sensitivity for MIN( ) and MAX( )
6.	What are the lowest and highest U.S. state populations?
7.	To make bstr not case sensitive, you can convert the values to a given lettercase:
8.	What are the shortest and longest trips in the mytable table?

Returns the minimum value from a group of values: MIN() : MIN « Aggregate Functions « SQL / MySQL

Related examples in the same category