Using math function in HAVING : Group « Select Clause « SQL / MySQL

Home
SQL / MySQL
 1 Aggregate Functions 2 Backup Load 3 Command MySQL 4 Cursor 5 Data Type 6 Database 7 Date Time 8 Engine 9 Event 10 Flow Control 11 FullText Search 12 Function 13 Geometric 14 I18N 15 Insert Delete Update 16 Join 17 Key 18 Math 19 Procedure Function 20 Regular Expression 21 Select Clause 22 String 23 Table Index 24 Transaction 25 Trigger 26 User Permission 27 View 28 Where Clause 29 XML
 SQL / MySQL » Select Clause » Group
Using math function in HAVING
 ```    /* 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);                                   ```
Related examples in the same category
 1 Use GROUP BY clause to list only the unique data 2 Another GROUP BY 3 Use GROUP BY 4 GROUP BY with order and HAVING 5 Use GROUP BY 2 6 Use GROUP BY and ORDER BY together 7 Get GROUP BY for COUNT 8 Simple GROUP BY 9 GROUP and sort the records 10 GROUP value and count 11 Grouping Data: Filtering Group Data 12 Grouping Data: 03 Using the HAVING Clause 1 13 Grouping Data: Using the HAVING Clause 1 14 GROUP and HAVING with sub query 15 Group by and order by for linked tables 16 Grouping by Expression Results 17 Give the expression an alias in the output column list and refer to the alias in the GROUP BY clause 18 Write the GROUP BY clause to refer to the output column position: 19 Group by multiple expressions if you like. 20 Group mail table records into categories of 100,000 bytes 21 Group by DAYOFWEEK( ) 22 Working with Per-Group and Overall Summary Values Simultaneously 23 Finding Rows Containing Per-Group Minimum or Maximum Values 24 Maximum-per-group problem for this table 25 Another way to group statements is to turn off auto-commit mode explicitly. 26 To use a GROUP BY clause effectively, you should also include a select list element that contains a function t 27 Specifies two columns in the GROUP BY clause 28 Working with Grouped Data 29 Group by calculated value 30 Group value in subquery 31 Group by two columns 32 Group by then order by vs Group by only 33 GROUP BY for Several Columns 34 GROUP BY returns a final sum for the first column and supplementary partial sums for the second column. 35 Dividing a Summary into Subgroups 36 Use the name column to place the rows in groups, but the summary functions operate on the miles values: 37 Parentheses may be used to group alternations. 38 To be more specific and find out how many messages each sender sent from each host, use two grouping columns. 39 Categorize groups on a logarithmic scale. 40 Missing and non-missing counts can be determined for subgroups as well. 41 get the number of orders per customer 42 A less fine-grained summary can be obtained by using only the month values 43 Find out how many books you have per author, use this query 44 Ascertain the most common initial letter for state names like this: