Question 89681 in PostgreSQL Question posted in
The official documentation can be found here.

Correct Postgresql SQL for query involving COUNT and joined tables

I have a database on sporting events whose schema can be summarized like this:

Teams
--------
id
name

Events
-------
id
nickname
date
team_1 (links to teams.id)
team_2 (links to teams.id)

Plays
-----
id
event  (links to events.id)
<other details>

I want to say things like, "For each event after such and such date, give me the nickname, the date, the names of the two teams, and the number of plays recorded." My attempt looks like this:

  SELECT COUNT(plays), events.nickname, events.start, team1.name, team2.name
  FROM plays
  JOIN events ON plays.event = events.id
  JOIN teams AS team1 ON events.team_1 = team1.id
  JOIN teams AS team2 ON events.team_2 = team2.id

  WHERE events.start > '2023-02-01'
  GROUP BY events.id

But it tells me:

error: column "team1.name" must appear in the GROUP BY clause or be
used in an aggregate function

I have seen this error in cases where it would be truly illogical, where the aggregation produces less records than the rest of the query. But that is not true in this case. So what is the correct syntax for asking this query?

Tags:

3

Answers


  1. 0

    What I would suggest is the below. This used a DERIVED TABLE (T3) to get you a count for every event. This keeps the group by separate from your main query.

    T3.PlaysCount will show NULL if there are no plays. To get around this you can do ,COALESCE(T3.PlaysCount, 0) which will show 0 instead of NULL.

    Additionally, if you don’t want to show events with 0 plays, change the LEFT JOIN to a JOIN

    SELECT T0.NickName
      ,T0.Date
      ,T1.Name
      ,T2.Name
      ,T3.PlaysCount
FROM Events T0
JOIN Teams T1
ON T1.ID = T0.Team_1
JOIN Teams T2
ON T2.ID = T0.Team_2
LEFT JOIN (
    SELECT T0.Event
          ,COUNT(T0.ID) AS PlaysCount
    FROM Plays T0
    GROUP BY T0.Event
) T3
ON T3.Event = T0.ID
WHERE T0.Start > '2023-02-01'
    Login or Signup to reply.
  2. 0

    All column in the output of group by should be either part of group by condition or a aggregated column. I guess those columns are identified with respect to events.id. so you can simply use max() function to pick of the value out. see the following code as an example:

      SELECT 
      COUNT(plays), 
      MAX(events.nickname), 
      MAX(events.start), 
      MAX(team1.name), 
      MAX(team2.name), 
      events.id
  FROM plays
  JOIN events ON plays.event = events.id
  JOIN teams AS team1 ON events.team_1 = team1.id
  JOIN teams AS team2 ON events.team_2 = team2.id

  WHERE events.start > '2023-02-01'
  GROUP BY events.id
    Login or Signup to reply.
  3. 0

    as the error message already said, all column in the SELECTmust appear in the GROUP BY or use aggregation functions

    But in your case it is isimle, as every column is unique and has no duplictaes for every eventid

      SELECT COUNT(plays), MIN(events.nickname) as nickname, MIN(events.start) as start, MIN(team1.name) as team1_name, MIN(team2.name) as team2_name
  FROM plays
  JOIN events ON plays.event = events.id
  JOIN teams AS team1 ON events.team_1 = team1.id
  JOIN teams AS team2 ON events.team_2 = team2.id

  WHERE events.start > '2023-02-01'
  GROUP BY events.id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search