I am writing a query to get all players for all teams. Instead of looping in the application, I decided to get the players of all teams in a single query using array_agg()
. I have written the query as follows:
SELECT team_id, array_agg(team_name) AS teamname, array_agg(player_id||'##'||player_name) AS playerdetails FROM team INNER JOIN players ON team_id = player_team GROUP BY team_id
This query gives me the result as below, in the result set the teamname
is being repeated (exactly to the no. of players)
team_id team_name playerdetails 1 {Australia,Australia,Australia,Australia} {"5##Glenn Donald McGrath","6##Shane Warne","2##Steve Waugh","1##Adam Gilchrist"} 2 {India,India,India,India} {"8##Kapil Dev","11##Saurav Ganguly","3##Rahul Dravid","9##Sachin Tendulkar"} 3 {"South Africa","South Africa","South Africa","South Africa"} {"12##Gary Kristen","4##Shaun Pollock","7##Jacques Kallis","10##Alan Donald"}
Is there any way to return the result like this
team_id team_name playerdetails 1 Australia {"5##Glenn Donald McGrath","6##Shane Warne","2##Steve Waugh","1##Adam Gilchrist"}
I have achieved it using a subquery, but want to know if its possible to write it without the subquery
SELECT team_id, teamname[1], playerdetails FROM ( SELECT team_id, array_agg(team_name) AS teamname, array_agg(player_id||'##'||player_name) AS playerdetails FROM team INNER JOIN players ON team_id = player_team GROUP BY team_id) AS tempresult
The sqfiddle is here. And I am using Postgresql 8.4
[EDIT]
I was actually thinking of hack to the GROUP BY
limitation column "team.team_status" must appear in the GROUP BY clause or be used in an aggregate function
when try to retrieve the a column which was not been specified in group by
SELECT team_id, array_agg(team_name) AS teamname, array_agg(player_id||'##'||player_name) AS playerdetails, team_status -- could be replaced by something like array_agg(team_status)[0] or customfunction(team_status) FROM team INNER JOIN players ON team_id = player_team GROUP BY team_id
It was actually a mistake from my part... the answer to my first question lies in that query itself. I just have to enclose the (array_agg(team_name))[1]
, earlier I tried it without the brackets.
SELECT team_id, (array_agg(team_name))[1] AS teamname, array_agg(player_id||'##'||player_name) AS playerdetails FROM team INNER JOIN players ON team_id = player_team GROUP BY team_id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With