How to add default value on sum to have equal number of lines sum-CodePudding

I'm in front of a problem that regarding MySQL.

I have three tables in my Database :

Table gp

____________
id | name | 
____________
1 | Le Mans|     
2 | Toulon |    
3 | Rennes |

Table player

____________
id | name | 
____________
1 | Thibaut|     
2 | Fred |    
3 | Samir |

Table Records

_____________________________
id | gp_id | player_id | time  
_____________________________
1 | 1 | 1 | 22    
2 | 2 | 1 | 33
3 | 3 | 1 | 44
4 | 3 | 2 | 40
5 | 2 | 2 | 35  
6 | 1 | 2 | 20
7 | 1 | 3 | 25
8 | 3 | 3 | 38

I want to get a sum of time for players that have at least one record on some specifics GP and to set a default time for the gp where they don't have a time

I have no idea how I can get that. Actually my SQL query get the values but I don't know how to set a default time for GP not finished by some players.

SELECT p.name, sum(time) as total_time
from records r 
join gp g on r.gp_id = g.id 
join player p on r.player_id = p.id
where gp.id in ( 1, 2, 3)
having count(distinct g.id) > 0
group by r.player_id

For example for this query I get these values :

name | total_time
_________________
Thibaut | 99    
Fred | 95
Samir | 63

But I want a default time to 99 if there is no time for a player in one GP, so in my case Samir should have 63 99 => 162. But I have no idea how to do that, and I don't know if it's possible

Thanks in advance guys !

CodePudding user response：

Use LEFT JOIN to get a null value if there's no match, and IFNULL() to supply a default value in place of NULL.

Use a CROSS JOIN with gp to specify all the games that should be considered.

SELECT p.name, sum(IFNULL(time, 99)) as total_time
from player p 
cross join gp
left join records r on r.player_id = p.id AND r.gp_id = gp.id
WHERE gp.id IN (1, 2, 3)
group by p.id
having count(distinct r.gp_id) > 0

DEMO