SQL/MySQL/Math/Average

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

Getting Column Averages

/*
mysql> SELECT AVG(Mark) AS AverageMark
    -> FROM StudentExam
    -> WHERE StudentID = 10;
+-------------+
| AverageMark |
+-------------+
|     73.3333 |
+-------------+
1 row in set (0.00 sec)

*/
/* Create table */
Drop TABLE StudentExam;
CREATE TABLE StudentExam (
   StudentID  INT NOT NULL,
   Mark       INT,
   Comments   VARCHAR(255)
   
)TYPE = InnoDB;
/* Insert data */
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (10,76,"Java");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (10,65,"C#");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (10,79,"JavaScript");

/* Real command */
SELECT AVG(Mark) AS AverageMark
FROM StudentExam
WHERE StudentID = 10;



Use AVG and alias

/*
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 avg(salary) AS "Average Salary" from employee;
+----------------+
| Average Salary |
+----------------+
|     89850.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 avg(salary) AS "Average Salary" from employee;



Use AVG and compare

/*
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 title, AVG(salary)
    -> from employee
    -> GROUP BY title
    -> HAVING AVG(salary) > 100000;
+----------------------------+-------------+
| title                      | AVG(salary) |
+----------------------------+-------------+
| Finance Manager            | 120000.0000 |
| Senior Marketing Executive | 120000.0000 |
| Senior Programmer          | 115000.0000 |
| Senior Web Designer        | 110000.0000 |
+----------------------------+-------------+
4 rows in set (0.02 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 title, AVG(salary)
from employee
GROUP BY title 
HAVING AVG(salary) > 100000;