user variables in a calculation

Question

I am producing a league table for speedway teams, I have got a query to calculate the required data based on teams results. The part I am struggling with is the points difference (difference between points scored and scored against.

    SELECT tbl_clubs.club, tbl_clubs.club_id,
SUM(if(tbl_clubs.club_id = tbl_fixtures.away AND tbl_fixtures.awayscore is not null ,1,0)) +
SUM(if(tbl_clubs.club_id = tbl_fixtures.home AND tbl_fixtures.homescore is not null,1,0 )) as `M`,
SUM( if( tbl_clubs.club_id = tbl_fixtures.home
AND tbl_fixtures.homescore > tbl_fixtures.awayscore, 1, 0 ) ) AS `W`,
SUM( IF( tbl_clubs.club_id = tbl_fixtures.home AND tbl_fixtures.awayscore = tbl_fixtures.homescore, 1, 0 ) ) AS `HD`,
SUM( if( tbl_clubs.club_id = tbl_fixtures.home
AND tbl_fixtures.homescore < tbl_fixtures.awayscore, 1, 0 ) ) AS `HL`,
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore > tbl_fixtures.homescore
AND tbl_fixtures.awayscore - tbl_fixtures.homescore >=7,1,0)) AS `4W`,
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore > tbl_fixtures.homescore
AND tbl_fixtures.awayscore - tbl_fixtures.homescore <=6,1,0)) AS `3W`,
SUM( IF( tbl_clubs.club_id = tbl_fixtures.away AND tbl_fixtures.awayscore = tbl_fixtures.homescore, 1, 0 ) ) AS `AD`,
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore < tbl_fixtures.homescore
AND tbl_fixtures.homescore - tbl_fixtures.awayscore <=6,1,0)) AS `1L`,

SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore < tbl_fixtures.homescore
AND tbl_fixtures.homescore - tbl_fixtures.awayscore >=7,1,0)) AS `L`,

@FOR:=SUM(IF(tbl_clubs.club_id = tbl_fixtures.away,tbl_fixtures.awayscore,0)) +
SUM(IF(tbl_clubs.club_id = tbl_fixtures.home,tbl_fixtures.homescore,0)) as `F`,


@Against:=SUM(IF(tbl_clubs.club_id = tbl_fixtures.home,tbl_fixtures.awayscore,0)) +
SUM(IF(tbl_clubs.club_id = tbl_fixtures.away,tbl_fixtures.homescore,0)) as `A`,

SUM(@For - @Against) as `PtsDiff`,

SUM( if( tbl_clubs.club_id = tbl_fixtures.home
AND tbl_fixtures.homescore > tbl_fixtures.awayscore, 3, 0 ) ) +
SUM( IF( tbl_clubs.club_id = tbl_fixtures.home AND tbl_fixtures.awayscore = tbl_fixtures.homescore, 1, 0 ) ) +
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore > tbl_fixtures.homescore
AND tbl_fixtures.awayscore - tbl_fixtures.homescore >=7,4,0)) +
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore > tbl_fixtures.homescore
AND tbl_fixtures.awayscore - tbl_fixtures.homescore <=6,3,0)) +
SUM( IF( tbl_clubs.club_id = tbl_fixtures.away AND tbl_fixtures.awayscore = tbl_fixtures.homescore, 2, 0 ) ) +
SUM(if(tbl_clubs.club_id = tbl_fixtures.away
AND tbl_fixtures.awayscore < tbl_fixtures.homescore
AND tbl_fixtures.homescore - tbl_fixtures.awayscore <=6,1,0))  as `Pts`
FROM tbl_clubs
INNER JOIN tbl_fixtures ON tbl_clubs.club_id = tbl_fixtures.home

OR tbl_clubs.club_id = tbl_fixtures.away
where tbl_clubs.league_id = 3
GROUP BY tbl_clubs.club_id
order by Pts desc, PtsDiff desc, club asc

All of the query is working except for

@FOR:=SUM(IF(tbl_clubs.club_id = tbl_fixtures.away,tbl_fixtures.awayscore,0)) +
SUM(IF(tbl_clubs.club_id = tbl_fixtures.home,tbl_fixtures.homescore,0)) as `F`,


@Against:=SUM(IF(tbl_clubs.club_id = tbl_fixtures.home,tbl_fixtures.awayscore,0)) +
SUM(IF(tbl_clubs.club_id = tbl_fixtures.away,tbl_fixtures.homescore,0)) as `A`,

SUM(@For - @Against) as `PtsDiff`,

I am getting NULL as a result for this, I am guessing its something simple, any help would be great

score 0 · Answer 1 · answered Apr 03 '17 at 12:43

Firstly, you'll want to ensure that null values always default to 0 and not null. This is something that could happen within your script, so you should ensure that 0 is the only viable default. This should occur before this query gets executed though ideally. It doesn't appear that this is the case based on your script, so you may want to ensure you have designed your database to cater for that.

Secondly, variable assignment inside queries is not quite as straight-forward as you'd like it to be unfortunately. You may be better using a sub-query for your initialisations:

@FOR:=(select IFNULL(SUM(tbl_fixtures.awayscore),0)  from tbl_fixtures where tbl_clubs.club_id = tbl_fixtures.away) +   
        (select IFNULL(SUM(tbl_fixtures.homescore),0) from tbl_fixtures where tbl_clubs.club_id = tbl_fixtures.home)
as `F`,
@Against:=(select IFNULL(SUM(tbl_fixtures.awayscore),0)  from tbl_fixtures where tbl_clubs.club_id = tbl_fixtures.home) +   
        (select IFNULL(SUM(tbl_fixtures.homescore),0) from tbl_fixtures where tbl_clubs.club_id = tbl_fixtures.away)
as `A`, 
SUM(@FOR - @Against) as `PtsDiff`,

Not very elegant, but this should work. Notice the use of IFNULL to ensure that null is converted to 0.

user variables in a calculation

1 Answers1