Confused with the Group By function in SQL-CodePudding

Q1: After using the Group By function, why does it only output one row of each group at most? Does this mean that having is supposed to filter the group rather than filter the records in each group?

Q2: I want to find the records in each group whose ages are greater than the average age of that group. I tried the following, but it returns nothing. How should I fix this?

SELECT *, avg(age) FROM Mytable Group By country Having age > avg(age)

Thanks!!!!

CodePudding user response：

You can calculate the average age for each country in a subquery and join that to your table for filtering:

SELECT mt.*, MtAvg.AvgAge 
FROM Mytable mt

     inner join
     (
       select mtavgs.country
            , avg(mtavgs.age) as AvgAge 
       from Mytable mtavgs
       group by mtavgs.country
      ) MTAvg
      on mtavg.country=mt.country
        and mt.Age > mtavg.AvgAge

GROUP BY returns always 1 row per unique combination of values in the GROUP BY columns listed (provided that they are not removed by a HAVING clause). The subquery in our example (alias: MTAvg) will calculate a single row per country. We will use its results for filtering the main table rows by applying the condition in the INNER JOIN clause; we will also report that average by including the calculated average age.

CodePudding user response：

GROUP BY is a keyword that is called an aggregate function. Check this out here for further reading SQL Group By tutorial

What it does is it lumps all the results together into one row. In your example it would lump all the results with the same country together.

Not quite sure what exactly your query needs to be to solve your exact problem. I would however look into what are called window functions in SQL. I believe what you first need to do is write a window function to find the average age in each group. Then you can write a query to return the results you need