Search code examples
sqlpostgresqlgroup-bycountansi-sql

How Postgres/ANSI SQL systems relate 'count' aggregates to GROUP BY fields

I'm trying to understand both the group by and the count(*) in the following SQL query:

select Fizz.countryId, Buzz.kindId, Buzz.razId, count(*)
from Foo
  join Buzz on Foo.BuzzId = Buzz.id
  join Fizz on Foo.FizzId = Fizz.id
where
  Foo.borrowed > CURRENT_DATE - INTERVAL '6 months'
group by
  Fizz.countryId, Buzz.kindId, Buzz.razId;

So we're joining three tables: Fizz, Buzz and Foo. Then we're filtering out records out of that joined table whose Foo.borrowed field is older than 6 months. But what I'm not understanding is how count(*) is being calculated and how it relates to the fields mentioned in the group by clause. Any ideas?

Solution

  • In the result only one row will be shown for every distinct combination of Fizz.countryId, Buzz.kindId, Buzz.razId. That's what group by does.

    Therefore, each resulting row may represent many original rows. How many? count(*) tells you.