Soccer/sport all time table in mysql

Question

I look to made all time soccer table.
I use to table:

• Teams: with info about club: ID, name, team actual name
  
• FinalTables: with all final table, season by season and level by level
  

Query is

SELECT
    t.team Team,
    SUM(CASE WHEN (f.TeamActualName = t.team) THEN 1 ELSE 0 END) Seasons,
    SUM(CASE WHEN (f.TeamActualName = t.team) THEN f.utakmica ELSE 0 END) P,
    SUM(CASE WHEN (f.TeamActualName = t.team) THEN f.pobjede ELSE 0 END) W,
    SUM(CASE WHEN (f.TeamActualName = t.team) THEN f.nerješeni ELSE 0 END) D,
    SUM(CASE WHEN (f.TeamActualName = t.team) THEN f.porazi ELSE 0 END) L,
    SUM(CASE WHEN (f.TeamActualName = t.team) THEN f.postigao ELSE 0 END) GF,
    SUM(CASE WHEN (f.TeamActualName = t.team) THEN f.primio ELSE 0 END) GA,
    SUM(CASE WHEN (f.TeamActualName = t.team) THEN f.postigao-f.primio ELSE 0 END) GD,
    SUM(CASE WHEN (f.TeamActualName = t.team) THEN f.bodovi ELSE 0 END) Points

FROM
    FinalTables AS f,
    Teams AS t

WHERE
    f.level = 1

GROUP BY
    t.id

ORDER BY
    Points DESC,
    GD DESC,
    GF DESC,
    Team ASC

This query work great.

I have two problem.

1. First as in Teams table is listed all club who play during years independent of level, in all time table I receive also lot of club with zero game. For example not all teams play on first level, so all other have zero game. How to avoid it, so receive table only with club who play at least one game on first level, like in example.

2. Second question is how to add in first column order number. So who have most points are "1.", and this to end, 2, 3, 4.....
   

All my trying was without success. Some help? :)

Answer 1

First off, I know this isn't your question, but you've done the join between f and t in a very ... creative way.

Second, I'm going to assume that every team is listed in the Teams table. I had a bit of trouble understanding your #1 question, however if this isn't the case, it should be. There should also be a foreign key set on FinalTables to enforce that any team listed there must also be in Teams.

Third, I'm not an expert in MySql specifically, but it took very little time to Google a lot of answers to your ranking question. I'm going to link to one (Rank function in MySQL) and implement its recommendation in my answer below, but I can't test it. I'm guessing any downvotes you've gotten are related to the lack of research attempt. Don't let it discourage you though - we all come here for answers when we get stuck.

I'd rewrite your SQL like this (just keep in mind that it may have a typo or may need tweaking - I'm unable to test it on a MySql installation myself):

SELECT
    t.team Team,
    COUNT(*) Seasons,
    SUM(f.utakmica) P,
    SUM(f.pobjede) W,
    SUM(f.nerješeni) D,
    SUM(f.porazi) L,
    SUM(f.postigao) GF,
    SUM(f.primio) GA,
    SUM(f.postigao-f.primio) GD,
    SUM(f.bodovi) Points,
    @curRank := @curRank + 1 AS rank
FROM
    Teams AS t, 
    (SELECT @curRank := 0)
    LEFT JOIN FinalTables AS f on f.TeamActualName = t.team
WHERE
    f.level = 1
GROUP BY
    t.id
ORDER BY
    Points DESC,
    GD DESC,
    GF DESC,
    Team ASC