Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Having Clause

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

       Having Clause

      MySQL Having Clause

      The MySQL HAVING Clause is used to filter grouped rows in a table based on conditions.
      This clause is used with the GROUP BY clause to group the rows based on one or more columns and then filter them based on the conditions specified in the HAVING clause. So, the HAVING clause must always be followed by the GROUP BY clause.
      The HAVING clause was added to MySQL because the WHERE keyword cannot be used with aggregate functions such as COUNT(), SUM(), AVG(), etc.
      This clause is similar to the MySQL WHERE clause. The difference between both of them is that the WHERE clause filters individual rows in a table, whereas the HAVING clause filters grouped rows based on conditions.

      Syntax

      Following is the basic syntax of the HAVING clause in MySQL 
      SELECT column1, column2, aggregate_function(column)
      FROM table_name
      GROUP BY column1, column2, ...
      HAVING condition
      ORDER BY column1, column2, ...;

      Example

      Let us begin with creating a table named CUSTOMERS using the following query 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR (20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),   
         PRIMARY KEY (ID)
      );
      The following INSERT statement inserts 7 records into the above-created table 
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
      (2, 'Khilan', 25, 'Delhi', 1500.00 ),
      (3, 'Kaushik', 23, 'Kota', 2000.00 ),
      (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
      (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
      (6, 'Komal', 22, 'Hyderabad', 4500.00 ),
      (7, 'Muffy', 24, 'Indore', 10000.00 );
      Using the following query, we can verify whether the CUSTOMERS table is created or not 
      SELECT * FROM CUSTOMERS;
      Following is the CUSTOMERS table
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00

      HAVING clause with ORDER BY clause

      In MySQL, the HAVING clause filters the groups, and the ORDER BY clause sorts the results. When we used both of them together, HAVING is executed first, then the result set is sorted according to the ORDER BY criteria.

      Example

      In the following query, we are retrieving all the records from the CUSTOMERS table where the sum of their SALARY is less than 4540, ordered by their name in ascending order 
      SELECT NAME, SUM(SALARY) as total_salary
      FROM CUSTOMERS
      GROUP BY NAME
      HAVING SUM(SALARY) < 4540
      ORDER BY NAME;

      Output

      The output for the query above is produced as given below
      NAME
      total_salary
      Kaushik
      2000.00
      Khilan
      1500.00
      Komal
      4500.00
      Ramesh
      2000.00

      HAVING clause with COUNT() function

      We can use the MySQL HAVING clause in conjunction with the COUNT() function to filter the groups based on the number of rows they contain.

      Example

      In this query, we are fetching a record where the count of similar age is greater than or equal to 2.
      SELECT AGE
      FROM CUSTOMERS
      GROUP BY age
      HAVING COUNT(age) >= 2;

      Output

      There are two records in CUSTOMERS table with age 25, thus the output is 25
      AGE
      25

      HAVING clause with AVG() function

      The MySQL HAVING clause can also be used with the AVG() function to filter groups based on the average value of a specified column.

      Example

      In the following query, we are trying to return the names of the customers whose salary is greater than 3000 
      SELECT NAME, AVG(salary) as avg_salary
      FROM customers
      GROUP BY NAME
      HAVING AVG(salary) > 3000;

      Output

      The output for the query above is produced as given below
      NAME
      avg_salary
      Chaitali
      6500.000000
      Hardik
      8500.000000
      Komal
      4500.000000
      Muffy
      10000.000000

      HAVING clause with MAX() function

      In MySQL, we can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column.

      Example

      In this query, we are retrieving the customer names whose maximum SALARY is less than 4000 
      SELECT NAME, MAX(salary) as max_salary
      FROM customers
      GROUP BY NAME
      HAVING MAX(salary) < 4000;

      Output

      On executing the given query, the output is displayed as follows
      NAME
      max_salary
      Ramesh
      2000.00
      Khilan
      1500.00
      Kaushik
      2000.00

      Having Clause Using a Client Program

      Besides using MySQL HAVING clause to filter grouped rows in a table based on conditions, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

      Syntax

      Following are the syntaxes of this operation in various programming languages
      PHPNodeJSJavaPython
      To filter grouped rows in a table based on conditions through PHP program, we need to execute SELECT statement with HAVING clause using the mysqli function query() as follows 
      $sql = "SELECT EXPRESSION1, EXPRESSION2, ...EXPRESSION_N, 
      AGGREGATE_FUNCTION(EXPRESSION) FROM TABLE_NAME 
      [WHERE CONDITION] GROUP BY EXPRESSION1, 
      EXPRESSION2.. EXPRESSION_N HAVING CONDITION";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      if($mysqli->connect_errno ) {
         printf("Connect failed: %s", $mysqli->connect_error);
         exit();
      }
      //printf('Connected successfully.');
      $sql = 'SELECT tutorial_title, count(tutorial_id) AS tot_count FROM tutorials_tbl WHERE tutorial_id > 1 GROUP BY tutorial_title HAVING count(tutorial_id) > 1';
      $result = $mysqli->query($sql);
      if ($result->num_rows > 0) {
         printf("Table records: \n");
         while($row = $result->fetch_assoc()) {
            printf("Title: %s, Count: %d",  
               $row["tutorial_title"],
               $row["tot_count"]);
            printf("\n");         
         }
      } else {
         printf('No record found.');
      }
      mysqli_free_result($result);
      $mysqli->close();

      Output

      The output obtained is as follows 
      Table records:
      Title: Learn MySQL, Count: 2