SQL/MySQL/Select Clause/Group

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

Another GROUP BY

   <source lang="sql">

/* 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.00 sec) mysql> /*Use the following GROUP BY clause to list only the unique cities and st ates in the cust_city and cust_state columns of the duck_cust table. mysql> */ mysql> SELECT name, category FROM sales

   -> GROUP BY name;

+------------+------------+ | name | category | +------------+------------+ | C | Profession | | Cplus | Literary | | Java | Holiday | | JavaScript | Literary | | MySQL | Literary | | Oracle | Holiday | | PHP | Profession | | Python | Holiday | | SQL | Profession | +------------+------------+ 9 rows 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;

/*Use the following GROUP BY clause to list only the unique cities and states in the cust_city and cust_state columns of the duck_cust table.

  • /

SELECT name FROM sales GROUP BY name;


      </source>
   
  


Get GROUP BY for COUNT

   <source lang="sql">

/* 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, count(*)

   -> from employee GROUP BY title;

+----------------------------+----------+ | title | count(*) | +----------------------------+----------+ | Customer Service Manager | 1 | | Finance Manager | 1 | | Marketing Executive | 3 | | Multimedia Programmer | 3 | | Programmer | 4 | | Senior Marketing Executive | 1 | | Senior Programmer | 2 | | Senior Web Designer | 1 | | System Administrator | 2 | | Web Designer | 2 | +----------------------------+----------+ 10 rows 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 title, count(*) from employee GROUP BY title;

      </source>
   
  


GROUP and HAVING with sub query

   <source lang="sql">

/* mysql> SELECT ArticleID, SUM(Quantity) AS Total

   -> FROM ArticleOrders
   -> GROUP BY ArticleID
   -> HAVING ArticleID IN
   ->    (SELECT ArticleID FROM AuthorArticle WHERE AuthID IN
   ->       (
   ->          SELECT AuthID FROM AuthorArticle
   ->          GROUP BY AuthID
   ->          HAVING COUNT(*)>1
   ->       )
   ->    );

+-----------+-------+ | ArticleID | Total | +-----------+-------+ | 19264 | 1 | | 19354 | 3 | +-----------+-------+ 2 rows in set (0.01 sec)

  • /

Drop table Articles; Drop table Authors; Drop table AuthorArticle; Drop table ArticleOrders;

CREATE TABLE Articles (

  ArticleID SMALLINT NOT NULL PRIMARY KEY,
  ArticleTitle VARCHAR(60) NOT NULL,
  Copyright YEAR NOT NULL

) ENGINE=INNODB;

INSERT INTO Articles VALUES (12786, "How write a paper", 1934),

                           (13331, "Publish a paper", 1919),
                           (14356, "Sell a paper", 1966),
                           (15729, "Buy a paper", 1932),
                           (16284, "Conferences", 1996),
                           (17695, "Journal", 1980),
                           (19264, "Information", 1992),
                           (19354, "AI", 1993);

CREATE TABLE Authors (

  AuthID SMALLINT NOT NULL PRIMARY KEY,
  AuthorFirstName VARCHAR(20),
  AuthorMiddleName VARCHAR(20),
  AuthorLastName VARCHAR(20)

) ENGINE=INNODB;

INSERT INTO Authors VALUES (1006, "Henry", "S.", "Thompson"),

                          (1007, "Jason", "Carol", "Oak"),
                          (1008, "James", NULL, "Elk"),
                          (1009, "Tom", "M", "Ride"),
                          (1010, "Jack", "K", "Ken"),
                          (1011, "Mary", "G.", "Lee"),
                          (1012, "Annie", NULL, "Peng"),
                          (1013, "Alan", NULL, "Wang"),
                          (1014, "Nelson", NULL, "Yin");

CREATE TABLE AuthorArticle (

  AuthID SMALLINT NOT NULL,
  ArticleID SMALLINT NOT NULL,
  PRIMARY KEY (AuthID, ArticleID),
  FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
  FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)

) ENGINE=INNODB;

INSERT INTO AuthorArticle VALUES (1006, 14356),

                             (1008, 15729), 
                             (1009, 12786), 
                             (1010, 17695),
                             (1011, 15729), 
                             (1012, 19264), 
                             (1012, 19354), 
                             (1014, 16284);

CREATE TABLE ArticleOrders (

  OrderID SMALLINT NOT NULL,
  ArticleID SMALLINT NOT NULL,
  Quantity SMALLINT NOT NULL,
  PRIMARY KEY (OrderID, ArticleID),
  FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)

) ENGINE=INNODB;

INSERT INTO ArticleOrders VALUES (101, 13331, 1),

                                (101, 12786, 1), 
                                (101, 16284, 2), 
                                (102, 19354, 1),
                                (102, 15729, 3), 
                                (103, 12786, 2), 
                                (103, 19264, 1), 
                                (103, 13331, 1),
                                (103, 14356, 2), 
                                (104, 19354, 1), 
                                (105, 15729, 1), 
                                (105, 14356, 2),
                                (106, 16284, 2), 
                                (106, 13331, 1), 
                                (107, 12786, 3), 
                                (108, 19354, 1),
                                (108, 16284, 4), 
                                (109, 15729, 1), 
                                (110, 13331, 2), 
                                (110, 12786, 2),
                                (110, 14356, 2), 
                                (111, 14356, 2);
 
 

SELECT ArticleID, SUM(Quantity) AS Total FROM ArticleOrders GROUP BY ArticleID HAVING ArticleID IN

  (SELECT ArticleID FROM AuthorArticle WHERE AuthID IN
     (
        SELECT AuthID FROM AuthorArticle
        GROUP BY AuthID
        HAVING COUNT(*)>1
     )
  );


      </source>
   
  


GROUP and sort the records

   <source lang="sql">

/* 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.06 sec) mysql> select children, count(*) AS

   ->        number from employee_person
   ->        GROUP BY children
   ->        ORDER BY number DESC;

+----------+--------+ | children | number | +----------+--------+ | NULL | 10 | | 3 | 4 | | 2 | 4 | | 1 | 2 | | 4 | 1 | +----------+--------+ 5 rows 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 children, count(*) AS

      number from employee_person
      GROUP BY children
      ORDER BY number DESC;
          
      </source>
   
  


GROUP BY with order and HAVING

   <source lang="sql">

/* 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.00 sec) mysql> /* Using the following SELECT command with WHERE, GROUP BY and HAVING cla uses, list the design_name or names in the Literary design_category that have wi nter_sales over 700, in order of most sales to least sales. mysql> */ mysql> SELECT name, category, winter

   -> FROM sales
   -> WHERE category="Literary"
   -> GROUP BY winter DESC HAVING winter > 700;

+-------+----------+--------+ | name | category | winter | +-------+----------+--------+ | MySQL | Literary | 953 | | Cplus | Literary | 752 | +-------+----------+--------+ 2 rows 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;

/* Using the following SELECT command with WHERE, GROUP BY and HAVING clauses, list the design_name or names in the Literary design_category that have winter_sales over 700, in order of most sales to least sales.

*/

SELECT name, category, winter FROM sales WHERE category="Literary" GROUP BY winter DESC HAVING winter > 700;

      </source>
   
  


Grouping Data: 03 Using the HAVING Clause 1

   <source lang="sql">

/* mysql> Select * from StudentExam; +-----------+------+------------+ | StudentID | Mark | Comments | +-----------+------+------------+ | 10 | 76 | Java | | 10 | 65 | C# | | 10 | 79 | JavaScript | | 11 | 66 | Java | | 11 | 85 | C# | | 11 | 69 | JavaScript | +-----------+------+------------+ 6 rows in set (0.00 sec) mysql> /* Real command */ mysql> SELECT StudentID, AVG(Mark) AS AverageMark

   -> FROM StudentExam
   -> GROUP BY StudentID
   -> HAVING AVG(Mark) < 50 OR AVG(Mark) > 70;

+-----------+-------------+ | StudentID | AverageMark | +-----------+-------------+ | 10 | 73.3333 | | 11 | 73.3333 | +-----------+-------------+ 2 rows in set (0.01 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"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,66,"Java"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,85,"C#"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,69,"JavaScript"); Select * from StudentExam; /* Real command */

SELECT StudentID, AVG(Mark) AS AverageMark FROM StudentExam GROUP BY StudentID HAVING AVG(Mark) < 50 OR AVG(Mark) > 70;

      </source>
   
  


Grouping Data: Filtering Group Data

   <source lang="sql">

/* mysql> Select * from StudentExam; +-----------+------+------------+ | StudentID | Mark | Comments | +-----------+------+------------+ | 10 | 76 | Java | | 10 | 65 | C# | | 10 | 79 | JavaScript | | 11 | 66 | Java | | 11 | 85 | C# | | 11 | 69 | JavaScript | +-----------+------+------------+ 6 rows in set (0.00 sec) mysql> /* Real command */ mysql> SELECT StudentID, COUNT(*) AS HighPasses

   -> FROM StudentExam
   -> WHERE Mark > 70
   -> GROUP BY StudentID;

+-----------+------------+ | StudentID | HighPasses | +-----------+------------+ | 10 | 2 | | 11 | 1 | +-----------+------------+ 2 rows in set (0.04 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"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,66,"Java"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,85,"C#"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,69,"JavaScript"); Select * from StudentExam; /* Real command */

SELECT StudentID, COUNT(*) AS HighPasses FROM StudentExam WHERE Mark > 70 GROUP BY StudentID;

      </source>
   
  


Grouping Data: Using the HAVING Clause 1

   <source lang="sql">

/* 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"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,66,"Java"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,85,"C#"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,69,"JavaScript"); Select * from StudentExam; /* Real command */ SELECT StudentID, AVG(Mark) AS AverageMark FROM StudentExam WHERE ExamID IN ( 5, 8, 11 ) GROUP BY StudentID HAVING AVG(Mark) < 50 OR AVG(Mark) > 70;

      </source>
   
  


GROUP value and count

   <source lang="sql">

/* 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.01 sec) mysql> select sex, count(*)

   ->        from employee_person
   ->        GROUP BY sex;

+------+----------+ | sex | count(*) | +------+----------+ | M | 16 | | F | 5 | +------+----------+ 2 rows 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 sex, count(*)

      from employee_person 
      GROUP BY sex;


      </source>
   
  


Simple GROUP BY

   <source lang="sql">

/* 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.02 sec) mysql> select title from employee

   -> GROUP BY title;

+----------------------------+ | title | +----------------------------+ | Customer Service Manager | | Finance Manager | | Marketing Executive | | Multimedia Programmer | | Programmer | | Senior Marketing Executive | | Senior Programmer | | Senior Web Designer | | System Administrator | | Web Designer | +----------------------------+ 10 rows 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 title from employee GROUP BY title;

      </source>
   
  


Use GROUP BY

   <source lang="sql">
 

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;

/*Change the GROUP BY field to cust_city, to list all the unique cities and their states, including both cities from CA.

  • /

SELECT * FROM sales GROUP BY catagory;


      </source>
   
  


Use GROUP BY 2

   <source lang="sql">

/* 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.01 sec) mysql> select title, AVG(salary)

   -> from employee
   -> GROUP BY title;

+----------------------------+-------------+ | title | AVG(salary) | +----------------------------+-------------+ | Customer Service Manager | 70000.0000 | | Finance Manager | 120000.0000 | | Marketing Executive | 77333.3333 | | Multimedia Programmer | 83333.3333 | | Programmer | 75000.0000 | | Senior Marketing Executive | 120000.0000 | | Senior Programmer | 115000.0000 | | Senior Web Designer | 110000.0000 | | System Administrator | 95000.0000 | | Web Designer | 87500.0000 | +----------------------------+-------------+ 10 rows 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 title, AVG(salary) from employee GROUP BY title;

      </source>
   
  


Use GROUP BY and ORDER BY together

   <source lang="sql">

/*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, count(*) AS Number

   -> from employee
   -> GROUP BY title
   -> ORDER BY Number;

+----------------------------+--------+ | title | Number | +----------------------------+--------+ | Customer Service Manager | 1 | | Finance Manager | 1 | | Senior Web Designer | 1 | | Senior Marketing Executive | 1 | | System Administrator | 2 | | Senior Programmer | 2 | | Web Designer | 2 | | Marketing Executive | 3 | | Multimedia Programmer | 3 | | Programmer | 4 | +----------------------------+--------+ 10 rows 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 title, count(*) AS Number from employee GROUP BY title ORDER BY Number;

      </source>
   
  


Use GROUP BY clause to list only the unique data

   <source lang="sql">

/* 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> /* Use the following GROUP BY clause to list only the unique states in th e cust_state column of the duck_cust table. mysql> */ mysql> SELECT * FROM sales

   -> GROUP BY category;

+-----+------------+--------+--------+--------+------+------------+ | num | name | winter | spring | summer | fall | category | +-----+------------+--------+--------+--------+------+------------+ | 1 | Java | 1067 | 200 | 150 | 267 | Holiday | | 3 | JavaScript | 53 | 13 | 21 | 856 | Literary | | 2 | C | 970 | 770 | 531 | 486 | Profession | +-----+------------+--------+--------+--------+------+------------+ 3 rows 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;

/* Use the following GROUP BY clause to list only the unique states in the cust_state column of the duck_cust table.

*/

SELECT * FROM sales GROUP BY category;


      </source>
   
  


Using math function in HAVING

   <source lang="sql">

/* mysql> SELECT OrderID, SUM(Quantity) AS Total

   -> FROM ArticleOrders
   -> GROUP BY OrderID
   -> HAVING Total>(SELECT AVG(Quantity) FROM ArticleOrders);

+---------+-------+ | OrderID | Total | +---------+-------+ | 101 | 4 | | 102 | 4 | | 103 | 6 | | 105 | 3 | | 106 | 3 | | 107 | 3 | | 108 | 5 | | 110 | 6 | | 111 | 2 | +---------+-------+ 9 rows in set (0.05 sec)

  • /

Drop table Articles; Drop table Authors; Drop table AuthorArticle; Drop table ArticleOrders;

CREATE TABLE Articles (

  ArticleID SMALLINT NOT NULL PRIMARY KEY,
  ArticleTitle VARCHAR(60) NOT NULL,
  Copyright YEAR NOT NULL

) ENGINE=INNODB;

INSERT INTO Articles VALUES (12786, "How write a paper", 1934),

                           (13331, "Publish a paper", 1919),
                           (14356, "Sell a paper", 1966),
                           (15729, "Buy a paper", 1932),
                           (16284, "Conferences", 1996),
                           (17695, "Journal", 1980),
                           (19264, "Information", 1992),
                           (19354, "AI", 1993);

CREATE TABLE Authors (

  AuthID SMALLINT NOT NULL PRIMARY KEY,
  AuthorFirstName VARCHAR(20),
  AuthorMiddleName VARCHAR(20),
  AuthorLastName VARCHAR(20)

) ENGINE=INNODB;

INSERT INTO Authors VALUES (1006, "Henry", "S.", "Thompson"),

                          (1007, "Jason", "Carol", "Oak"),
                          (1008, "James", NULL, "Elk"),
                          (1009, "Tom", "M", "Ride"),
                          (1010, "Jack", "K", "Ken"),
                          (1011, "Mary", "G.", "Lee"),
                          (1012, "Annie", NULL, "Peng"),
                          (1013, "Alan", NULL, "Wang"),
                          (1014, "Nelson", NULL, "Yin");

CREATE TABLE AuthorArticle (

  AuthID SMALLINT NOT NULL,
  ArticleID SMALLINT NOT NULL,
  PRIMARY KEY (AuthID, ArticleID),
  FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
  FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)

) ENGINE=INNODB;

INSERT INTO AuthorArticle VALUES (1006, 14356),

                             (1008, 15729), 
                             (1009, 12786), 
                             (1010, 17695),
                             (1011, 15729), 
                             (1012, 19264), 
                             (1012, 19354), 
                             (1014, 16284);

CREATE TABLE ArticleOrders (

  OrderID SMALLINT NOT NULL,
  ArticleID SMALLINT NOT NULL,
  Quantity SMALLINT NOT NULL,
  PRIMARY KEY (OrderID, ArticleID),
  FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)

) ENGINE=INNODB;

INSERT INTO ArticleOrders VALUES (101, 13331, 1),

                                (101, 12786, 1), 
                                (101, 16284, 2), 
                                (102, 19354, 1),
                                (102, 15729, 3), 
                                (103, 12786, 2), 
                                (103, 19264, 1), 
                                (103, 13331, 1),
                                (103, 14356, 2), 
                                (104, 19354, 1), 
                                (105, 15729, 1), 
                                (105, 14356, 2),
                                (106, 16284, 2), 
                                (106, 13331, 1), 
                                (107, 12786, 3), 
                                (108, 19354, 1),
                                (108, 16284, 4), 
                                (109, 15729, 1), 
                                (110, 13331, 2), 
                                (110, 12786, 2),
                                (110, 14356, 2), 
                                (111, 14356, 2);
 

SELECT OrderID, SUM(Quantity) AS Total FROM ArticleOrders GROUP BY OrderID HAVING Total>(SELECT AVG(Quantity) FROM ArticleOrders);


      </source>