SQL aggregate query error

Question

I have 3 tables like this

player(id,name,age,teamid)
team(id,name,sponsor,totalplayer,totalchampion,boss,joindate)
playerdetail(id,playerid,position,number,allstar,joindate)

I want to select teaminfo include name,sponsor,totalplayer,totalchampion,boss, the average age of the players, the number of the allstar players

I write the t-sql as below

SELECT T.NAME,T.SPONSOR,T.TOTALPLAYER,T.TOTALCHAMPION,T.BOSS,T.JOINDATE,
AVG(P.AGE) AS AverageAge,COUNT(D.ALLSTAR) As AllStarPlayer
FROM Team T,Player P,PlayerDetail D 
WHERE T.ID=P.TID AND P.ID=D.PID

but it doesn't work, the error message is

'Column 'Team.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

Who can help me? Thx in advance!

What sort of field is playerdetail.allstar? Unless it's NULL when a player is not an allstar, this will return the number of (non-null) playerdetail records, not how many allstars are on a team. — , Dec 29 '11 at 10:49

score 3 · Accepted Answer · edited May 23 '17 at 12:03

3

Add

GROUP BY 
      T.NAME,T.SPONSOR,T.TOTALPLAYER,T.TOTALCHAMPION,T.BOSS,T.JOINDATE

In most RDBMS (except MySQL which will guess for you), a column must be either aggregated (COUNT, AVG) or in the GROUP BY

Also, you should use explicit JOINs.
This is clearer, less ambiguous and more difficult to bollix your code

SELECT 
    T.NAME, T.SPONSOR, T.TOTALPLAYER, T.TOTALCHAMPION, T.BOSS, T.JOINDATE,
    AVG(P.AGE) AS AverageAge,
    COUNT(D.ALLSTAR) As AllStarPlayer
FROM 
    Team T
    JOIN
    Player P ON T.ID=P.TID
    JOIN
    PlayerDetail D  ON P.ID=D.PID
GROUP BY 
    T.NAME, T.SPONSOR, T.TOTALPLAYER, T.TOTALCHAMPION, T.BOSS, T.JOINDATE;

edited May 23 '17 at 12:03

Community

1
1

answered Dec 29 '11 at 10:01

gbn

422,506
82
585
676

Hi, gbn, my all star is bit type, so when the player is not an allstar, the value is 0, I think I can use count() to calculate the total number of allstars. Your t-sql works well. Thanks! My original t-sql as same as yours except without the group by statement, could you tell me why I have to add the group by statement? – James Dec 30 '11 at 07:19
@AI_Bryant: "In most RDBMS (except MySQL which will guess for you), a column must be either aggregated (COUNT, AVG) or in the GROUP BY" – gbn Dec 30 '11 at 07:32

score 1 · Answer 2 · answered Dec 29 '11 at 10:52

1

Given that you want this data per team, and team.ID uniquely identifies team, I suggest the following:

SELECT max(T.NAME) As TeamName,
       max(T.SPONSOR) As Sponsor,
       max(T.TOTALPLAYER) As TotalPlayers,
       max(T.TOTALCHAMPION) As TotalChampions,
       max(T.BOSS) As Boss,
       max(T.JOINDATE) As JoinDate,
       AVG(P.AGE) AS AverageAge,
       COUNT(D.PID) As AllStarPlayer
FROM Team T
join Player P on T.ID=P.TID 
left join PlayerDetail D on P.ID=D.PID and D.ALLSTAR = 'Y'
group by T.ID

answered Dec 29 '11 at 10:52

A workaround for a MySQL-addicted developer? :) Actually, I like it, though I'm almost certain that I'll never use it. – Andriy M Dec 29 '11 at 12:01
? Not sure which bit you think is the workaround, or why you mention MySQL specifically... unless it's the use of MAX (where you wouldn't need an aggregate function in MySQL)? I find redundant grouping to be confusing, when trying to understand SQL queries. – Dec 29 '11 at 12:20
Yes, I was talking about the MAXes for the columns that aren't supposed to change since their PK is in the GROUP BY clause. I see your concern, but this abundance of aggregating looks hardly less confusing to me. I mean, if you are used to it, fine. I would aggregate `Player left join PlayerDetail` separately, then join the result set to `Team`, though. – Andriy M Dec 29 '11 at 12:49
Through a subquery? Still more confusing (to me), and likely to perform poorly (in my experience). – Dec 29 '11 at 12:51

Espen Burud · Answer 3 · 2011-12-29T10:09:06.637

0

Use:

SELECT T.NAME,T.SPONSOR,T.TOTALPLAYER,T.TOTALCHAMPION,T.BOSS,T.JOINDATE,
AVG(P.AGE) AS AverageAge,COUNT(D.ALLSTAR) As AllStarPlayer
FROM Team T
JOIN Player P ON T.ID = P.TEAMID
JOIN PlayerDetail D ON P.ID = D.PLAYERID
GROUP BY T.NAME,T.SPONSOR,T.TOTALPLAYER,T.TOTALCHAMPION,T.BOSS,T.JOINDATE

edited Dec 29 '11 at 10:09

answered Dec 29 '11 at 10:03

Espen Burud

1,871
10
9

SQL aggregate query error

3 Answers3