---
title: "How to Use Group By and Having in SQL Query: Best Example"
description: "Here is an SQL query explained to get duplicate records from a table"
url: https://srinimf.com/2023/05/26/how-to-use-sql-having-clause-correctly-in-sql-query/
date: 2023-05-26
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2022/10/pexels-photo-5239601.jpeg?fit=1880%2C1255&ssl=1
categories: ["SQL"]
tags: ["Having Clause Example"]
type: post
lang: en
---

# How to Use Group By and Having in SQL Query: Best Example

Here is an [SQL query](https://srinimf.com/2013/04/01/db2-raise-error-function/) that can help you find duplicate records. This query shows [how to](https://srinimf.com/2015/05/27/reduce-sql-complexity-with-grouping-sets-in-db2-v11/) use the “having” and “count” functions for this purpose.

Related[5 More Frequently Asked DB2 SQL Queries](https://srinimf.com/2015/05/15/5-more-frequently-asked-db2-sql-queries/)

ON THIS PAGE

Related[238 Top SQL Queries for ETL/Oracle/DB2 Developers](https://srinimf.com/2014/01/20/238-top-sql-queries/)

1. [Create a table and insert rows](https://srinimf.com/2023/05/26/how-to-use-sql-having-clause-correctly-in-sql-query/#create-a-table-and-insert-rows)
2. [Read Table data](https://srinimf.com/2023/05/26/how-to-use-sql-having-clause-correctly-in-sql-query/#read-table-data)
3. [SQL query to get duplicate records](https://srinimf.com/2023/05/26/how-to-use-sql-having-clause-correctly-in-sql-query/#sql-query-to-get-duplicate-records)

## Create a table and insert rows

The[Create table query](https://srinimf.com/2017/11/28/sql-about-table-popular-ideas-to-begin/)creates table of my_table. It has two columns. One is root id, and the other one is hero_id. And inserted some rows into the table.

```
create table my_table(my_num number, root_id number, hero_id number);
insert into my_table
values
(
01, 1001, 2111);
insert into my_table
values
(
02, 1002, 2111);
insert into my_table
values
(
03, 1003, 2112);
select * from my_table;
```

## Read Table data

Use SELECT query and get the table data. Look at the output, the hero_id[contains duplicates](https://learnsql.com/blog/how-to-find-duplicate-values-in-sql/). In the next query, you’ll see [how to get](https://srinimf.com/2021/11/06/how-to-get-cpu-time-for-sql-query/) these duplicates.

| MY_NUM | ROOT_ID | HERO_ID |
| --- | --- | --- |
| 1 | 1001 | **2111** |
| 2 | 1002 | **2111** |
| 3 | 1003 | 2112 |

## [SQL query to](https://srinimf.com/2015/12/22/best-db2-sql-queries-to-work-on-duplicate-values/) get duplicate records

The hero_id contains duplicates. To get duplicates, use the having clause and Count>1.

```
select hero_id from my_table
group by hero_id
having count(*) > 1;
```

You can see in the output which value of hero_id has duplicates.

| HERO_ID |
| --- |
| 2111 |

*Output*

References

- [Oracle 19C for Developers and Testers](https://www.amazon.in/dp/B08W3HNDYZ?tag=srinimf-21)

Related

- [SQL Query to Remove, get Only Duplicates](https://srinimf.com/2015/12/22/best-db2-sql-queries-to-work-on-duplicate-values/)DateDecember 22, 2015
- [6 DB2 SQL Set Operators](https://srinimf.com/2015/02/02/db2-set-operators/)DateFebruary 2, 2015
- [3 Must-know Tips Before You Write SQL Query](https://srinimf.com/2018/05/13/sql-code-review-tips/)DateMay 13, 2018

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2023%2F05%2F26%2Fhow-to-use-sql-having-clause-correctly-in-sql-query%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2022%2F10%2Fpexels-photo-5239601.jpeg%3Ffit%3D1200%252C801%26ssl%3D1&description=How%20to%20Use%20Group%20By%20and%20Having%20in%20SQL%20Query%3A%20Best%20Example)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2023/05/26/how-to-use-sql-having-clause-correctly-in-sql-query/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2023/05/26/how-to-use-sql-having-clause-correctly-in-sql-query/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2023/05/26/how-to-use-sql-having-clause-correctly-in-sql-query/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2023/05/26/how-to-use-sql-having-clause-correctly-in-sql-query/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Use%20Group%20By%20and%20Having%20in%20SQL%20Query%3A%20Best%20Example&body=https%3A%2F%2Fsrinimf.com%2F2023%2F05%2F26%2Fhow-to-use-sql-having-clause-correctly-in-sql-query%2F&share=email)
-

### Like this:

Like Loading…