SQL/MySQL/Math/Sum — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 10:16, 26 мая 2010

Another SUM

/*mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title                      | age  | yearofservice| salary | perks | email               |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
|  1 | John      | Chen     | Senior Programmer          |   31 |             3| 120000 | 25000 | j@hotmail.ru       |
|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4| 110000 | 20000 | g@yahoo.ru         |
|  3 | Ane       | Pandit   | Web Designer               |   24 |             3|  90000 | 15000 | a@gmail.ru         |
|  4 | Mary      | Anchor   | Web Designer               |   27 |             2|  85000 | 15000 | m@mail.ru          |
|  5 | Fred      | King     | Programmer                 |   32 |             3|  75000 | 15000 | f@net.ru           |
|  6 | John      | Mac      | Programmer                 |   32 |             4|  80000 | 16000 | j@hotmail.ru       |
|  7 | Arthur    | Sam      | Programmer                 |   28 |             2|  75000 | 14000 | e@yahoo.ru         |
|  8 | Alok      | Nanda    | Programmer                 |   32 |             3|  70000 | 10000 | a@yahoo.ru         |
|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4|  90000 | 15000 | h@gmail.ru         |
| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1|  85000 | 12000 | ps@gmail.ru        |
| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2|  75000 | 15000 | a@hotmail.ru       |
| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4| 110000 | 20000 | kim@coolmail.ru    |
| 13 | Roger     | Lewis    | System Administrator       |   32 |             3| 100000 | 13000 | roger@mail.ru      |
| 14 | Danny     | Gibson   | System Administrator       |   31 |             2|  90000 | 12000 | danny@hotmail.ru   |
| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1| 120000 | 28000 | m@gmail.ru         |
| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5|  90000 | 25000 | monica@bigmail.ru  |
| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1|  70000 | 18000 | hal@gmail.ru       |
| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1|  72000 | 18000 | joseph@hotmail.ru  |
| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3|  70000 |  9000 | shahida@hotmail.ru |
| 20 | Peter     | Champion | Finance Manager            |   32 |             2| 120000 | 25000 | peter@yahoo.ru     |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.00 sec)
mysql> select SUM(yearofservice) from employee;
+--------------------+
| SUM(yearofservice) |
+--------------------+
|                 53 |
+--------------------+
1 row in set (0.00 sec)
*/  
Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
select SUM(yearofservice) from employee;



Do calculation with SUM

/*
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title                      | age  | yearofservice| salary | perks | email               |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
|  1 | John      | Chen     | Senior Programmer          |   31 |             3| 120000 | 25000 | j@hotmail.ru       |
|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4| 110000 | 20000 | g@yahoo.ru         |
|  3 | Ane       | Pandit   | Web Designer               |   24 |             3|  90000 | 15000 | a@gmail.ru         |
|  4 | Mary      | Anchor   | Web Designer               |   27 |             2|  85000 | 15000 | m@mail.ru          |
|  5 | Fred      | King     | Programmer                 |   32 |             3|  75000 | 15000 | f@net.ru           |
|  6 | John      | Mac      | Programmer                 |   32 |             4|  80000 | 16000 | j@hotmail.ru       |
|  7 | Arthur    | Sam      | Programmer                 |   28 |             2|  75000 | 14000 | e@yahoo.ru         |
|  8 | Alok      | Nanda    | Programmer                 |   32 |             3|  70000 | 10000 | a@yahoo.ru         |
|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4|  90000 | 15000 | h@gmail.ru         |
| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1|  85000 | 12000 | ps@gmail.ru        |
| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2|  75000 | 15000 | a@hotmail.ru       |
| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4| 110000 | 20000 | kim@coolmail.ru    |
| 13 | Roger     | Lewis    | System Administrator       |   32 |             3| 100000 | 13000 | roger@mail.ru      |
| 14 | Danny     | Gibson   | System Administrator       |   31 |             2|  90000 | 12000 | danny@hotmail.ru   |
| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1| 120000 | 28000 | m@gmail.ru         |
| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5|  90000 | 25000 | monica@bigmail.ru  |
| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1|  70000 | 18000 | hal@gmail.ru       |
| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1|  72000 | 18000 | joseph@hotmail.ru  |
| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3|  70000 |  9000 | shahida@hotmail.ru |
| 20 | Peter     | Champion | Finance Manager            |   32 |             2| 120000 | 25000 | peter@yahoo.ru     |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.00 sec)
mysql> /*
mysql> displays the percentage of salary that is given as perks to the employees
 of Bignet.
mysql> */
mysql> select (SUM(perks)/SUM(salary) * 100)
    ->        from employee;
+--------------------------------+
| (SUM(perks)/SUM(salary) * 100) |
+--------------------------------+
|                          18.92 |
+--------------------------------+
1 row in set (0.00 sec)
*/
Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
  
    
select * from employee;    
/*
displays the percentage of salary that is given as perks to the employees of Bignet.
*/
select (SUM(perks)/SUM(salary) * 100)                                     
       from employee;



Retrieving Column Totals

/*
mysql> SELECT SUM(Credits) AS TotalCredits FROM Course;
+--------------+
| TotalCredits |
+--------------+
|           13 |
+--------------+
1 row in set (0.02 sec)

*/       
/* Create the table */
Drop TABLE Course;
CREATE TABLE Course (
   CourseID INT NOT NULL PRIMARY KEY,
   Name     VARCHAR(50),
   Credits  INT)
TYPE = InnoDB;
/* Insert data */
INSERT INTO Course (CourseID,Name,Credits) VALUES (1,"Java",5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (2,"C#",5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (3,"JavaScript",3);
/* Real command */  
SELECT SUM(Credits) AS TotalCredits FROM Course;



Simplest SUM

/*
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title                      | age  | yearofservice| salary | perks | email               |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
|  1 | John      | Chen     | Senior Programmer          |   31 |             3| 120000 | 25000 | j@hotmail.ru       |
|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4| 110000 | 20000 | g@yahoo.ru         |
|  3 | Ane       | Pandit   | Web Designer               |   24 |             3|  90000 | 15000 | a@gmail.ru         |
|  4 | Mary      | Anchor   | Web Designer               |   27 |             2|  85000 | 15000 | m@mail.ru          |
|  5 | Fred      | King     | Programmer                 |   32 |             3|  75000 | 15000 | f@net.ru           |
|  6 | John      | Mac      | Programmer                 |   32 |             4|  80000 | 16000 | j@hotmail.ru       |
|  7 | Arthur    | Sam      | Programmer                 |   28 |             2|  75000 | 14000 | e@yahoo.ru         |
|  8 | Alok      | Nanda    | Programmer                 |   32 |             3|  70000 | 10000 | a@yahoo.ru         |
|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4|  90000 | 15000 | h@gmail.ru         |
| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1|  85000 | 12000 | ps@gmail.ru        |
| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2|  75000 | 15000 | a@hotmail.ru       |
| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4| 110000 | 20000 | kim@coolmail.ru    |
| 13 | Roger     | Lewis    | System Administrator       |   32 |             3| 100000 | 13000 | roger@mail.ru      |
| 14 | Danny     | Gibson   | System Administrator       |   31 |             2|  90000 | 12000 | danny@hotmail.ru   |
| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1| 120000 | 28000 | m@gmail.ru         |
| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5|  90000 | 25000 | monica@bigmail.ru  |
| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1|  70000 | 18000 | hal@gmail.ru       |
| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1|  72000 | 18000 | joseph@hotmail.ru  |
| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3|  70000 |  9000 | shahida@hotmail.ru |
| 20 | Peter     | Champion | Finance Manager            |   32 |             2| 120000 | 25000 | peter@yahoo.ru     |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.00 sec)
mysql> select SUM(age) from employee;
+----------+
| SUM(age) |
+----------+
|      603 |
+----------+
1 row in set (0.00 sec)
*/
Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
select SUM(age) from employee;



Use AVG and SUM together

/*
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title                      | age  | yearofservice| salary | perks | email               |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
|  1 | John      | Chen     | Senior Programmer          |   31 |             3| 120000 | 25000 | j@hotmail.ru       |
|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4| 110000 | 20000 | g@yahoo.ru         |
|  3 | Ane       | Pandit   | Web Designer               |   24 |             3|  90000 | 15000 | a@gmail.ru         |
|  4 | Mary      | Anchor   | Web Designer               |   27 |             2|  85000 | 15000 | m@mail.ru          |
|  5 | Fred      | King     | Programmer                 |   32 |             3|  75000 | 15000 | f@net.ru           |
|  6 | John      | Mac      | Programmer                 |   32 |             4|  80000 | 16000 | j@hotmail.ru       |
|  7 | Arthur    | Sam      | Programmer                 |   28 |             2|  75000 | 14000 | e@yahoo.ru         |
|  8 | Alok      | Nanda    | Programmer                 |   32 |             3|  70000 | 10000 | a@yahoo.ru         |
|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4|  90000 | 15000 | h@gmail.ru         |
| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1|  85000 | 12000 | ps@gmail.ru        |
| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2|  75000 | 15000 | a@hotmail.ru       |
| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4| 110000 | 20000 | kim@coolmail.ru    |
| 13 | Roger     | Lewis    | System Administrator       |   32 |             3| 100000 | 13000 | roger@mail.ru      |
| 14 | Danny     | Gibson   | System Administrator       |   31 |             2|  90000 | 12000 | danny@hotmail.ru   |
| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1| 120000 | 28000 | m@gmail.ru         |
| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5|  90000 | 25000 | monica@bigmail.ru  |
| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1|  70000 | 18000 | hal@gmail.ru       |
| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1|  72000 | 18000 | joseph@hotmail.ru  |
| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3|  70000 |  9000 | shahida@hotmail.ru |
| 20 | Peter     | Champion | Finance Manager            |   32 |             2| 120000 | 25000 | peter@yahoo.ru     |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.00 sec)
mysql> select SUM(salary), AVG(age)
    ->        from employee where
    ->        title = "Programmer";
+-------------+----------+
| SUM(salary) | AVG(age) |
+-------------+----------+
|      300000 |  31.0000 |
+-------------+----------+
1 row in set (0.00 sec)
*/  
Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
select SUM(salary), AVG(age)    
       from employee where
       title = "Programmer";



Use SUM and alias

/*
mysql> select * from sales;
+-----+------------+--------+--------+--------+------+------------+
| num | name       | winter | spring | summer | fall | category   |
+-----+------------+--------+--------+--------+------+------------+
|   1 | Java       |   1067 |    200 |    150 |  267 | Holiday    |
|   2 | C          |    970 |    770 |    531 |  486 | Profession |
|   3 | JavaScript |     53 |     13 |     21 |  856 | Literary   |
|   4 | SQL        |    782 |    357 |    168 |  250 | Profession |
|   5 | Oracle     |    589 |    795 |    367 |  284 | Holiday    |
|   6 | MySQL      |    953 |    582 |    336 |  489 | Literary   |
|   7 | Cplus      |    752 |    657 |    259 |  478 | Literary   |
|   8 | Python     |     67 |     23 |     83 |  543 | Holiday    |
|   9 | PHP        |    673 |     48 |    625 |   52 | Profession |
+-----+------------+--------+--------+--------+------+------------+
9 rows in set (0.01 sec)
mysql> SELECT SUM(winter) AS "winter total",
    -> SUM(spring) AS "spring total",
    -> SUM(summer) AS "summer total",
    -> SUM(fall) AS "fall total"
    -> FROM sales;
+--------------+--------------+--------------+------------+
| winter total | spring total | summer total | fall total |
+--------------+--------------+--------------+------------+
|         5906 |         3445 |         2540 |       3705 |
+--------------+--------------+--------------+------------+
1 row in set (0.00 sec)

*/
Drop table sales;
  
CREATE TABLE sales(
    num MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(20),
    winter INT,
    spring INT,
    summer INT,
    fall INT,
    category CHAR(13),
    primary key(num)
)type=MyISAM;

insert into sales value(1, "Java", 1067 , 200, 150, 267,"Holiday");
insert into sales value(2, "C",970,770,531,486,"Profession");
insert into sales value(3, "JavaScript",53,13,21,856,"Literary");
insert into sales value(4, "SQL",782,357,168,250,"Profession");
insert into sales value(5, "Oracle",589,795,367,284,"Holiday");
insert into sales value(6, "MySQL",953,582,336,489,"Literary");
insert into sales value(7, "Cplus",752,657,259,478,"Literary");
insert into sales value(8, "Python",67,23,83,543,"Holiday");
insert into sales value(9, "PHP",673,48,625,52,"Profession");
select * from sales;
  
SELECT SUM(winter) AS "winter total",
SUM(spring) AS "spring total",
SUM(summer) AS "summer total",
SUM(fall) AS "fall total"
FROM sales;



Use SUM and alias 2

/*mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title                      | age  | yearofservice| salary | perks | email               |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
|  1 | John      | Chen     | Senior Programmer          |   31 |             3| 120000 | 25000 | j@hotmail.ru       |
|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4| 110000 | 20000 | g@yahoo.ru         |
|  3 | Ane       | Pandit   | Web Designer               |   24 |             3|  90000 | 15000 | a@gmail.ru         |
|  4 | Mary      | Anchor   | Web Designer               |   27 |             2|  85000 | 15000 | m@mail.ru          |
|  5 | Fred      | King     | Programmer                 |   32 |             3|  75000 | 15000 | f@net.ru           |
|  6 | John      | Mac      | Programmer                 |   32 |             4|  80000 | 16000 | j@hotmail.ru       |
|  7 | Arthur    | Sam      | Programmer                 |   28 |             2|  75000 | 14000 | e@yahoo.ru         |
|  8 | Alok      | Nanda    | Programmer                 |   32 |             3|  70000 | 10000 | a@yahoo.ru         |
|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4|  90000 | 15000 | h@gmail.ru         |
| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1|  85000 | 12000 | ps@gmail.ru        |
| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2|  75000 | 15000 | a@hotmail.ru       |
| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4| 110000 | 20000 | kim@coolmail.ru    |
| 13 | Roger     | Lewis    | System Administrator       |   32 |             3| 100000 | 13000 | roger@mail.ru      |
| 14 | Danny     | Gibson   | System Administrator       |   31 |             2|  90000 | 12000 | danny@hotmail.ru   |
| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1| 120000 | 28000 | m@gmail.ru         |
| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5|  90000 | 25000 | monica@bigmail.ru  |
| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1|  70000 | 18000 | hal@gmail.ru       |
| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1|  72000 | 18000 | joseph@hotmail.ru  |
| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3|  70000 |  9000 | shahida@hotmail.ru |
| 20 | Peter     | Champion | Finance Manager            |   32 |             2| 120000 | 25000 | peter@yahoo.ru     |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.00 sec)
mysql> select (SUM(perks)/SUM(salary) * 100)
    -> AS "Perk Percentage" from
    -> employee;
+-----------------+
| Perk Percentage |
+-----------------+
|           18.92 |
+-----------------+
1 row in set (0.00 sec)
*/
Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
select (SUM(perks)/SUM(salary) * 100)
AS "Perk Percentage" from
employee;



Use SUM on int value

/*
mysql> select * from employee_person;
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
| id | address                             | phone   | email | birthday   | sex  | m_status | s_name          | children |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
|  1 | 200, Regina Street                  | 7176666 | net@hotmail.ru | 1971-04-26 | M    | Y        | Ane Regina      |     NULL |
|  2 | 1232 Alberta Road                   | 5553312 | jo@hotmail.ru | 1968-03-02 | M    | Y        | Jane Van        |        3 |
|  3 | 90 Potter A                         | 3331211 | gp@ymail.ru | 1967-09-22 | M    | N        | Sandhya Pil     |        2 |
|  4 | 202, Donna Street                   | 7176167 | twink@hotmail.ru | 1976-08-09 | F    | Y        | Manish Sha      |     NULL |
|  5 | Apartment #8, UBC, Van Street       | 8973242 | holy@ymail.ru | 1974-10-14 | F    | N        | NULL            |     NULL |
|  6 | 46 SFU Street                       | 6451234 | kill@el.ru | 1978-12-31 | M    | N        | NULL            |     NULL |
|  7 | 432 Mercury Ave                     | 7932232 | mac@hotmail.ru | 1966-08-21 | M    | Y        | Mary Shelly     |        3 |
|  8 | 8 Little YaleTown                   | 5442994 | edd@gmail.ru | 1975-01-14 | M    | N        | NULL            |     NULL |
|  9 | 64 Temp Road                        | 4327652 | nan@pmail.ru | 1969-05-19 | M    | Y        | Man Nanda       |        1 |
| 10 | 132 Metro House, Henry Street       | 5552376 | ra@hotmail.ru | 1968-07-06 | M    | N        | NULL            |     NULL |
| 11 | 1 Grace Town, Van Avenue            | 5433879 | soundofsilence@boxer.net | 1957-11-04 | M    | Y        | Muriel Lovelace |        4 |
| 12 | 97 Oakland Road                     | 5423311 | kingarthur@roundtable.org | 1968-02-15 | M    | Y        | Rina Brighton   |        3 |
| 13 | 543 Applegate Lane                  | 3434343 | levy@cmail.ru | 1968-09-03 | F    | Y        | Matt Shi        |        2 |
| 14 | 76 Fish Street                      | 7432433 | tink@email.ru | 1965-04-28 | M    | N        | NULL            |     NULL |
| 15 | 98 Gun Street                       | 6500787 | danny@fhardy.ru | 1966-06-23 | M    | Y        | Betty Cudly     |        3 |
| 16 | #5 Winnepag Homes                   | 5433243 | mike@cmail.ru | 1964-03-06 | M    | Y        | Stella Stevens  |        2 |
| 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@darling.ru | 1970-04-18 | F    | Y        | Edgar Alan      |        1 |
| 18 | Apartment #9, Together Towers       | 5476565 | odessey@hotmail.ru | 1973-10-09 | M    | N        | NULL            |     NULL |
| 19 | Apartment #9, West Towers           | 5476565 | jire@hotmail.ru | 1973-01-20 | M    | N        | NULL            |     NULL |
| 20 | 90 Yale Town                        | 7528326 | help@more.org | 1968-01-25 | F    | N        | NULL            |     NULL |
| 21 | 4329 Eucalyptus Avenue              | 4254863 | money@cold.ru | 1964-06-13 | M    | Y        | Ruby Richer     |        2 |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
21 rows in set (0.04 sec)
mysql> select sum(children) from employee_person;
+---------------+
| sum(children) |
+---------------+
|            26 |
+---------------+
1 row in set (0.00 sec)

*/
Drop table employee_person;
CREATE TABLE employee_person (
    id int unsigned not null primary key, 
    address varchar(60), 
    phone int, 
    email varchar(60), 
    birthday DATE, 
    sex ENUM("M", "F"), 
    m_status ENUM("Y","N"), 
    s_name varchar(40), 
    children int
);

INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, "200, Regina Street", 7176666, "net@hotmail.ru", "1971-04-26", "M", "Y", "Ane Regina");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, "1232 Alberta Road", 5553312, "jo@hotmail.ru", "1968-03-02", "M", "Y", "Jane Van", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, "90 Potter A", 3331211, "gp@ymail.ru", "1967-09-22", "M", "N", "Sandhya Pil", 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, "202, Donna Street", 7176167, "twink@hotmail.ru", "1976-08-09", "F", "Y", "Manish Sha");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, "Apartment #8, UBC, Van Street", 8973242, "holy@ymail.ru", "1974-10-14", "F", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, "46 SFU Street", "6451234", "kill@el.ru", "1978-12-31", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, "432 Mercury Ave", 7932232, "mac@hotmail.ru", "1966-8-21", "M", "Y", "Mary Shelly", "3");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, "8 Little YaleTown", 5442994, "edd@gmail.ru", "1975-01-14", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, "64 Temp Road", 4327652, "nan@pmail.ru", "1969-05-19", "M", "Y", "Man Nanda", "1");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, "132 Metro House, Henry Street", 5552376, "ra@hotmail.ru", "1968-07-06", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, "1 Grace Town, Van Avenue", 5433879, "soundofsilence@boxer.net", "1957-11-04", "M", "Y", "Muriel Lovelace", "4");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, "97 Oakland Road", 5423311, "kingarthur@roundtable.org", "1968-02-15", "M", "Y", "Rina Brighton", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, "543 Applegate Lane", 3434343, "levy@cmail.ru", "1968-09-03", "F", "Y", "Matt Shi", "2");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, "76 Fish Street", 7432433, "tink@email.ru", "1965-04-28", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, "98 Gun Street", 6500787, "danny@fhardy.ru", "1966-06-23", "M", "Y", "Betty Cudly", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, "#5 Winnepag Homes", 5433243, "mike@cmail.ru", "1964-03-06", "M", "Y", "Stella Stevens", 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, "652 Devon Building, 6th Jade Avenue", 5537885, "mona@darling.ru", "1970-04-18", "F", "Y", "Edgar Alan", 1);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, "Apartment #9, Together Towers", 5476565, "odessey@hotmail.ru", "1973-10-09", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, "Apartment #9, West Towers", 5476565, "jire@hotmail.ru", "1973-1-20", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, "90 Yale Town", 7528326, "help@more.org", "1968-01-25", "F", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, "4329 Eucalyptus Avenue", 4254863, "money@cold.ru", "1964-06-13", "M", "Y", "Ruby Richer", 2);
  
select * from employee_person;
  
select sum(children) from employee_person;
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Math/Sum&oldid=5153»