using sum and group by and ifnull

I have a table of money owed, along with a team identifier (could be 1,2,3 for example)

I have another table which gives a nam开发者_开发知识库e to these team identifiers (so 1 could refer to Team1, 2 could refer to John's jokers etc)

The first table can have multiple entries for money owed and I need to get the total owed per team identifier, and use the team name if it exists.

So I left join the tables and use a sum clause and get a total amount owed per teamname, or null if the teamname is not present. If it is null then I want to use the team identifier, so the results would look like

name            total 
.....................
team1            100  
John's jokers   1000  
99                50

where 99 is a team identifier because there was no teamname and there was a null present.

I tried using ifnull(columnName, teamID) but this failed when using a sum clause.

Could anyone help with this problem please

I think ifnull() is used like this:

select ifnull(teams.team_name, teams.team_id) from teams;

So in this case it tries to retrieve the name of the team, and if that comes back null it instead uses the team's identifier. In this case your query would look like this:

select ifnull(teams.team_name, owing.team_id), sum(amount_owed)
  from owing left join teams on owing.team_id = teams.id
 group by owing.team_id

Make sure the group by asks for the ID field from owing, not teams, otherwise you'll be grouping on a null field.

Does this resolve the issue?