--- description: The GROUP BY clause is a MYSQL command that is used to group rows that have the same values.The HAVING clause is used to restrict the results returned by the GROUP BY clause. title: MySQL GROUP BY and HAVING Clause with Examples image: https://www.guru99.com/images/sql-group-by-and-having.png --- [Skip to content](#main) **⚡ Smart Summary** SQL GROUP BY and HAVING clauses turn detailed rows into summary reports. GROUP BY collapses rows that share the same values into one row per group, while HAVING filters those groups after aggregate functions such as COUNT have been applied. * 📊 **Core Purpose:** GROUP BY groups rows with identical values and returns a single row for every grouped item. * 🧩 **Single Column Grouping:** Grouping the members table on gender collapses nine rows into two, one for Female and one for Male. * 🔗 **Multiple Column Grouping:** Grouping on two columns treats a row as unique when either value differs, so only exact duplicates collapse. * 🧮 **Aggregate Pairing:** COUNT, SUM, AVG, MIN, and MAX calculate one value per group, which produces the summary report. * 🚦 **HAVING Versus WHERE:** WHERE filters rows before grouping, HAVING filters the groups afterwards, and only HAVING accepts aggregate results. * ⚠️ **Strict Mode Caution:** Under ONLY\_FULL\_GROUP\_BY, every selected column must be grouped or wrapped in an aggregate function. [ Read More ](javascript:void%280%29;) ![SQL GROUP BY and HAVING Clause](https://www.guru99.com/images/sql-group-by-and-having.png) ## What is the SQL GROUP BY Clause? The GROUP BY clause is a SQL command that is used to **group rows that have the same values**. It is written inside the SELECT statement, and it is normally used together with aggregate functions to produce summary reports from the database. That is what it does: it **summarizes data** held in the database. Queries that contain the GROUP BY clause are called grouped queries, and they return a single row for every grouped item. ## SQL GROUP BY Syntax Now that the purpose of the clause is clear, look at the syntax of a basic grouped query. SELECT statements... GROUP BY column_name1[, column_name2, ...] [HAVING condition]; **HERE** * “**SELECT statements…**” is the standard [SQL SELECT](https://www.guru99.com/select-statement.html) command query. * “**GROUP BY** _column\_name1_” is the clause that performs the grouping based on column\_name1. * “**\[, column\_name2, …\]**” is optional and represents other column names when the grouping is done on more than one column. * “**\[HAVING condition\]**” is optional and is used to restrict the rows affected by the GROUP BY clause. It is similar to the [WHERE clause](https://www.guru99.com/where-clause.html), except that it is applied after the grouping. ## Grouping Using a Single Column The quickest way to see the effect of the SQL GROUP BY clause is to compare an ungrouped query with a grouped one. Start with a simple query that returns every gender entry in the members table. SELECT `gender` FROM `members`; | gender | | ------ | | Female | | Female | | Male | | Female | | Male | | Male | | Male | | Male | | Male | Nine rows are returned, and every value is repeated. Suppose we want the unique values for gender instead. The query below adds the GROUP BY clause. SELECT `gender` FROM `members` GROUP BY `gender`; Executing the above script in [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) against the myflixdb gives us the following results. | gender | | ------ | | Female | | Male | Note that only two rows have been returned, because the table holds only two gender types. The GROUP BY clause grouped all the “Male” members together and returned a single row for them, and it did the same with the “Female” members. ### RELATED ARTICLES * [MYSQL – ALTER, DROP, RENAME, MODIFY ](https://www.guru99.com/alter-drop-rename.html "MYSQL – ALTER, DROP, RENAME, MODIFY") * [How to Create Database in MySQL (Create MySQL Tables) ](https://www.guru99.com/how-to-create-a-database.html "How to Create Database in MySQL (Create MySQL Tables)") * [13 BEST SQL Books (2026 Update) ](https://www.guru99.com/best-sql-books.html "13 BEST SQL Books (2026 Update)") * [9 BEST Online SQL Compiler and Editors (2026) ](https://www.guru99.com/best-online-sql-compiler-editors.html "9 BEST Online SQL Compiler and Editors (2026)") ## Grouping Using Multiple Columns Grouping on one column is often too coarse for a real report. GROUP BY accepts a comma-separated list of columns, and the combination of their values defines each group. Suppose that we want a list of movie category\_id values and the corresponding years in which the movies were released. Observe the output of this simple query first. SELECT `category_id`, `year_released` FROM `movies`; | category\_id | year\_released | | ------------ | -------------- | | 1 | 2011 | | 2 | 2008 | | NULL | 2008 | | NULL | 2010 | | 8 | 2007 | | 6 | 2007 | | 6 | 2007 | | 8 | 2005 | | NULL | 2012 | | 7 | 1920 | | 8 | NULL | | 8 | 1920 | The highlighted rows show that the result contains duplicates. Executing the same query with GROUP BY removes them. SELECT `category_id`, `year_released` FROM `movies` GROUP BY `category_id`, `year_released`; Executing the above script in MySQL Workbench against the myflixdb gives us the following results shown below. | category\_id | year\_released | | ------------ | -------------- | | NULL | 2008 | | NULL | 2010 | | NULL | 2012 | | 1 | 2011 | | 2 | 2008 | | 6 | 2007 | | 7 | 1920 | | 8 | 1920 | | 8 | 2005 | | 8 | 2007 | The GROUP BY clause operates on both category\_id and year\_released to identify **unique** rows. The two duplicate rows for category 6 in 2007 collapsed into one. **Rule of thumb:** if the category id is the same but the year released is different, the row is treated as unique. If the category id and the year released are the same for more than one row, the rows are duplicates and only one of them is shown. ## Grouping and Aggregate Functions Removing duplicates is useful, but the real power of grouping appears when it is paired with [aggregate functions](https://www.guru99.com/aggregate-functions.html). An aggregate function calculates one value for each group: COUNT counts rows, SUM adds values, and AVG, MIN, and MAX describe the spread. Suppose we want the total number of male and female members in the database. The script below does that. SELECT `gender`, COUNT(`membership_number`) FROM `members` GROUP BY `gender`; Executing the above script in MySQL Workbench against the myflixdb gives us the following results. | gender | COUNT(\`membership\_number\`) | | ------ | ----------------------------- | | Female | 3 | | Male | 6 | The rows are grouped by every unique gender value, and the number of rows inside each group is counted by the COUNT aggregate function. The nine member records collapse into two summary rows. ### Restricting Query Results Using the HAVING Clause Groupings are not always wanted for every row in a table. Sometimes the report must be restricted to a given criterion, and that is the job of the HAVING clause. Suppose we want to know all the release years for movie category id 8\. The script below achieves that result. SELECT * FROM `movies` GROUP BY `category_id`, `year_released` HAVING `category_id` = 8; Executing the above script in MySQL Workbench against the myflixdb gives us the following results shown below. | movie\_id | title | director | year\_released | category\_id | | --------- | -------------------- | ------------ | -------------- | ------------ | | 9 | Honey mooners | John Schultz | 2005 | 8 | | 5 | Daddy’s Little Girls | NULL | 2007 | 8 | Only the movies with category id 8 have been kept by the HAVING condition. **Warning:** MySQL 5.7 and later enable the ONLY\_FULL\_GROUP\_BY mode by default, and under that mode SELECT \* with a GROUP BY clause is rejected, because movie\_id, title, and director are neither grouped nor aggregated. In production, name the grouped columns explicitly, for example _SELECT category\_id, year\_released FROM movies GROUP BY category\_id, year\_released HAVING category\_id = 8;_ ## WHERE vs HAVING vs GROUP BY vs ORDER BY Beginners frequently mix these four clauses, because all of them shape the result set. The difference lies in _when_ MySQL applies them: WHERE runs before the rows are grouped, HAVING runs after, and ORDER BY runs last of all. | Clause | What it does | When it runs | Accepts aggregate functions | | ----------------------------------------------------------------- | -------------------------------------------------------------- | --------------- | ------------------------------------- | | **WHERE** | Filters individual rows before any grouping. | Before GROUP BY | No | | **GROUP BY** | Collapses rows sharing the same values into one row per group. | After WHERE | Not applicable | | **HAVING** | Filters the groups produced by GROUP BY. | After GROUP BY | Yes, for example HAVING COUNT(\*) > 2 | | **[ORDER BY](https://www.guru99.com/order-by-desc-and-asc.html)** | Sorts the rows that survive the previous clauses. | Last | Yes, an aggregate alias can be sorted | The practical consequence is a performance one. Filtering with WHERE removes rows before the grouping work starts, so a condition that does not depend on an aggregate result belongs in WHERE rather than HAVING. ## FAQs 🧾 Can GROUP BY be used without an aggregate function? Yes. GROUP BY on its own returns one row per unique value, which removes duplicates in much the same way as SELECT DISTINCT. Aggregate functions are only required when each group needs a calculated figure. 🚫 Why does MySQL raise an ONLY\_FULL\_GROUP\_BY error? The error appears when a selected column is neither listed in GROUP BY nor wrapped in an aggregate function. MySQL cannot decide which value of that column to show for the group, so it refuses the query. 🧮 What is the difference between COUNT(\*) and COUNT(column)? COUNT(\*) counts every row in the group. COUNT(column) counts only the rows where that column is not [NULL](https://www.guru99.com/null.html), so the two figures differ whenever the column holds missing values. 🤖 Can AI write GROUP BY queries from plain-language questions? Yes. AI assistants inside tools such as [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) translate a request such as “members per gender” into a grouped query. Check the grouping columns yourself, because a wrong grouping produces totals that look plausible but are incorrect. 🧠 Can AI explain why a grouped total looks wrong? Often, yes. AI query assistants flag classic causes such as a [JOIN](https://www.guru99.com/joins.html) that multiplies rows before grouping, or a filter placed in HAVING instead of WHERE. The final judgement still belongs to the person who knows the data. #### Summarize this post with: ChatGPT Perplexity Grok Google AI **Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** ##### Sign up for the newsletter Subscribe for Free You have successfully subscribed. Please check your inbox. ![AI-Newsletter]() Chosen by over **350,000+** professionals [Scroll to top ](#wrapper)Scroll to top × Toggle Menu Close Search for: Search ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/sql-group-by-and-having.png","url":"https://www.guru99.com/images/sql-group-by-and-having.png","width":"700","height":"250","caption":"SQL GROUP BY & HAVING","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/group-by.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sql","name":"SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/group-by.html","name":"MySQL GROUP BY and HAVING Clause with Examples"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/group-by.html#webpage","url":"https://www.guru99.com/group-by.html","name":"MySQL GROUP BY and HAVING Clause with Examples","dateModified":"2026-07-14T11:56:15+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/sql-group-by-and-having.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/group-by.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen","description":"I'm Marcus Allen, an SQL and Data Warehousing Consultant with over a decade of experience in designing and optimizing large-scale data solutions.","url":"https://www.guru99.com/author/marcus","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/marcus-allen-author.png","url":"https://www.guru99.com/images/marcus-allen-author.png","caption":"Marcus Allen","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"SQL","headline":"MySQL GROUP BY and HAVING Clause with Examples","description":"The GROUP BY clause is a MYSQL command that is used to group rows that have the same values.The HAVING clause is used to restrict the results returned by the GROUP BY clause.","keywords":"sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen"},"dateModified":"2026-07-14T11:56:15+05:30","image":{"@id":"https://www.guru99.com/images/sql-group-by-and-having.png"},"copyrightYear":"2026","name":"MySQL GROUP BY and HAVING Clause with Examples","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"Can GROUP BY be used without an aggregate function?","acceptedAnswer":{"@type":"Answer","text":"Yes. GROUP BY on its own returns one row per unique value, which removes duplicates in much the same way as SELECT DISTINCT. Aggregate functions are only required when each group needs a calculated figure."}},{"@type":"Question","name":"Why does MySQL raise an ONLY_FULL_GROUP_BY error?","acceptedAnswer":{"@type":"Answer","text":"The error appears when a selected column is neither listed in GROUP BY nor wrapped in an aggregate function. MySQL cannot decide which value of that column to show for the group, so it refuses the query."}},{"@type":"Question","name":"What is the difference between COUNT(*) and COUNT(column)?","acceptedAnswer":{"@type":"Answer","text":"COUNT(*) counts every row in the group. COUNT(column) counts only the rows where that column is not NULL, so the two figures differ whenever the column holds missing values."}},{"@type":"Question","name":"Can AI write GROUP BY queries from plain-language questions?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI assistants inside tools such as MySQL Workbench translate a request such as \"members per gender\" into a grouped query. Check the grouping columns yourself, because a wrong grouping produces totals that look plausible but are incorrect."}},{"@type":"Question","name":"Can AI explain why a grouped total looks wrong?","acceptedAnswer":{"@type":"Answer","text":"Often, yes. AI query assistants flag classic causes such as a JOIN that multiplies rows before grouping, or a filter placed in HAVING instead of WHERE. The final judgement still belongs to the person who knows the data."}}]}],"@id":"https://www.guru99.com/group-by.html#schema-1143692","isPartOf":{"@id":"https://www.guru99.com/group-by.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/group-by.html#webpage"}}]} ```