Group by and having trouble understanding

group by not working
difference between group by and having in sql
group by without aggregate
group by oracle
group by order by
group by having order by in sql
sql group by everything
sql having max

I was looking at some SQL query that I have in Access database that I did not make.

One of the SQL query goes something like this:

select column1 from table1 group by column1 having count(*)>1

The purpose of this query is to find the value in column1 that appears more than once. I can verify that this query works correctly and returns the column value that appears more than once.

I however do not understand why this query works. As per my understanding using group by will remove duplicate fields. For instance if column1 had

    column1
    apple
    mango
    mango

Doing group by (column1) will result

    column1
    apple
    mango

At this point, if we perform having count(*)>1 or having count(column1)>1, it should return no result because group by has already removed the duplicate field. But clearly, I am wrong as the above SQL statement does give the accurate result.

Would you please let me know the problem in my understanding?

Edit 1:

Besides the accepted answer, I this article which deals with order of SQL operation really helped my understanding

You are misunderstanding how HAVING works. In fact, you can think of it by using subqueries. Your query is equivalent to:

select column1
from (select column1, count(*) as cnt
      from table1
      group by column1
     ) as t
having cnt > 1;

That is, having filters an aggregation query after the aggregation has taken place. However, the aggregation functions are applied per group. So count(*) is counting the number of rows in each group. That is why it is identifying duplicates.

Do You Really Understand SQL's GROUP BY and HAVING clauses , You are misunderstanding how HAVING works. In fact, you can think of it by using subqueries. Your query is equivalent to: select column1 from (select column1,  Not everyone knows that HAVING can be used all by itself, or what it even means to have HAVING all by itself. Already the SQL 1992 standard allowed for the use of HAVING without GROUP BY, but it wasn’t until the introduction of GROUPING SETS in SQL:1999, when the semantics of this syntax was retroactively unambiguously defined: 7.10 <having

group by doesn't just remove duplicate values - it returns one row per distinct value of the group by clause, and allows you apply aggregate function per such unique value.

In this query, you actually query the values of column1 and the result of count(*) per value of column1, then, you use the having clause to return only the values of column1 that have a count(*) greater than 1.

The New Encyclopedia of Icebreakers, Now, we cannot understand the grand total (empty GROUP BY ( ) clause), without having a short look at the SQL:1999 standard GROUPING  The GROUP BY Clause is used together with the SQL SELECT statement. The SELECT statement used in the GROUP BY clause can only be used contain column names, aggregate functions, constants and expressions. The HAVING clause is used to restrict the results returned by the GROUP BY clause.

GROUP BY clause groups the selection per the fields you mention, on this case column1 but can be a combined column (e.g. column1, column2). By the way, I think if you run:

SELECT column1, Count(*) AS [Count], MIN(column2) AS MinColumn2, MAX(column2) AS MaxColumn2
FROM table1 
GROUP BY column1;

Will help you to understand how GROUPING works. When filter by any column directly you may use the WHERE condition but if you want to filter per any field calculated from the grouping you need to use the HAVING clause.

CBT for Hoarding Disorder: A Group Therapy Program Therapist's Guide, Groups become frustrated as they try to understand what is expected of them. When this happens A group is having trouble understanding parts of the training  What if we want to group the information? The GROUP BY and HAVING clause helps you to group the resultant rows by a specific column. GROUP BY and HAVING clause is used with aggregate functions like Count, Max, Min, Sum, etc; This is easy to understand with an example. STEP 1: Create a relation for Student ( rollno, name, age);

Having Trouble Understanding Speech?, A Group Therapy Program Therapist's Guide David F. Tolin, Blaise L. Worden, the assignments because you are having trouble understanding them or have  HAVING filters records that work on summarized GROUP BY results. HAVING applies to summarized group records, whereas WHERE applies to individual records. Only the groups that meet the HAVING criteria will be returned. HAVING requires that a GROUP BY clause is present. WHERE and HAVING can be in the same query.

[PreCalculus] Having trouble understanding Group Algebra, In the study, 32 English-speaking subjects who had normal hearing were divided into groups based on age. The group of young adults, with an average age of 22,​  In this video we will learn 1. Grouping rows using GROUP BY 2. Filtering Groups 3. Difference between WHERE and HAVING clause in sql server Text version of t

Aphasia, [PreCalculus] Having trouble understanding Group Algebra. ✓ Answered. So basically, we just started learning about group algebra. We came up with like the​  This month's topic is one that often baffles inexperienced SQL coders. We will look at the GROUP BY clause, and then the difference between conditions placed in the WHERE clause, and the HAVING clause. I have used MySQL to test all of the examples, but most of them should work without any problems on other DBMS' too.

Comments
  • Having clause works on groups of rows that was formed as a result of grouping. The result of grouping was 2 groups ie apple and mango. On each of these groups we run the having clause. Apple group had 1 row and mango had 2. Thats how it works
  • The HAVING criteria will cause only Mango row to return because it is the only group HAVING a count >1.