CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Sadaf Ebrahimi
Sadaf Ebrahimi

Reputation: 41

Trouble with Group By and Having in SQL

I am trying to learn Group By and Having but I can't seem to understand what happened here. I used w3shools SQL Tryit Editor.

The table I created is:

name     age     country
------------------------
Sara     17      America
David    21      America
Jared    27      America
Jane     54      Canada
Rob      32      Canada
Matthew  62      Canada

The Query I used:

select 
    sum(age), country 
from 
    NewTable 
group by 
    country 
having 
    age>25;

I expected the query to categorize the information by country and use age>25 filter to create the results but here is the output:

sum(age)     country
--------------------
65           America
148          Canada

What happened?! The result is sum of American and Canadian people in all ages.

Upvotes: 1

Views: 1344

Answers (2)

Bohemian
Bohemian

Reputation: 425258

A where clause puts a condition on which rows participate in the results. A having clause is like a where, but puts a condition on which grouped (or aggregated) values participate in the results.

Either, try this:

select sum(age), country 
from NewTable
where age > 25 -- where puts condition on raw rows
group by country

or this:

select sum(age), country 
from NewTable 
group by country 
having sum(age) > 25 -- having puts a condition on groups

depending on what you're trying to do.

Upvotes: 1

Matt Legler
Matt Legler

Reputation: 69

The piece you're missing is specific to the having keyword. Using the having clause in your query is applied to the dataset after the grouping occurs.

It sounds like you are expecting the records with age less than 25 to be excluded from your query before grouping occurs. But, the way it works is the having clause excludes the total age for each group that sums to a total over 25.

If you want to exclude individual records before totaling the sum of the age, you could do something like this (using a where clause which is applied prior to grouping):

select sum(age), country from NewTable where age > 25 group by country;

Upvotes: 2

Related Questions