Finding Smallest or Largest Summary Values

      
mysql>
mysql> CREATE TABLE mytable
    -> (
    ->  rec_id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  name            VARCHAR(20) NOT NULL,
    ->  trav_date       DATE NOT NULL,
    ->  miles           INT NOT NULL,
    ->  PRIMARY KEY (rec_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO mytable (name,trav_date,miles)
    ->  VALUES
    ->          ('Ben','2010-11-30',152),
    ->          ('Suzi','2010-11-29',391),
    ->          ('Henry','2010-11-29',300),
    ->          ('Henry','2010-11-27',96),
    ->          ('Ben','2010-11-29',131),
    ->          ('Henry','2010-11-26',115),
    ->          ('Suzi','2010-12-02',502),
    ->          ('Henry','2010-12-01',197),
    ->          ('Ben','2010-12-02',79),
    ->          ('Henry','2010-11-30',203)
    -> ;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT name, SUM(miles)
    -> FROM mytable
    -> GROUP BY name;
+-------+------------+
| name  | SUM(miles) |
+-------+------------+
| Ben   |        362 |
| Henry |        911 |
| Suzi  |        893 |
+-------+------------+
3 rows in set (0.00 sec)

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

Related examples in the same category

1.	SUM() function returns the sum of the expression.
2.	SUM() the commission
3.	Sum by group
4.	Some aggregate functions can still produce NULL as a result.
5.	What is the total amount of mail traffic and the average size of each message?
6.	What is the total population of the United States?
7.	Summaries and NULL Values
8.	To determine how many drivers were on the road and how many miles were driven each day
9.	The number of votes for each programming language is determined with SUM(IF(...))
10.	To compute the runner's average speed at the end of each stage

Finding Smallest or Largest Summary Values : SUM « Aggregate Functions « SQL / MySQL

Related examples in the same category