Group by 1 for one column and others by 2

[bmbsa]

Hi,

I have a table with the following structure

team_id, player_id, field1, field2, field3, field4 …

I use the following MySQL query to return the results I need:

SELECT team_id, player_id, sum(field1) field1, sum(field2) field2, sum(field3) field3
FROM mytable
GROUP BY team_id, player_id

Is there anyway I can make the sum of field2 grouped by one column only (tema_id) instead of team_id, player_id and keep all other aggregations grouped by two columns.

I don’t prefer to use a sub-query as the table is very large and there are many joins and conditions.
What do you think?

[r937]

[quote=“bmbsa, post:1, topic:234294, full:true”]
Is there anyway I can make the sum of field2 grouped by one column only (tema_id) instead of team_id, player_id [/quote]
this is probably best done with a separate query, but good news, you can “force combine” the separate queries in a UNION

SELECT team_id , NULL AS player_id , NULL AS sum_field1 , SUM(field1) AS sum_field2 , NULL AS sum_field3 FROM mytable GROUP BY team_id UNION ALL SELECT team_id , player_id , SUM(field1) AS sum_field1 , NULL AS sum_field2 , SUM(field3) AS sum_field3 FROM mytable GROUP BY team_id , player_id ORDER BY team_id , player_id

[bmbsa]

Thanks Rudy!