How to add a field of a given ID with multiple records?

Question

The story is this: a politician can vote for several zonas, therefore the reason for the same ID candidate contain multiple records within the table.

The ID_CANDIDATO being 120000000171, he having 6 records in the database referring to the 6 different zones he received votes.

How to mount a sql that will show 1 registration with all the information of this candidate and the total of votes received by it?

ID        ID_CANDIDATO       TOT_VOTOS
21        120000000171       339
45        120000000171       405
73        120000000171       513
88        120000000171       380
92        120000000171       752
96        120000000171       439

A visual example of what I’m looking for is on this page. The last field is the TOT_VOTOS.

Answer 1

You can use the SUM. For this, you will need to group the records:

SELECT SUM(tot_votos) as total_votos FROM ... GROUP BY id_candidato

To get the candidate’s information in the same survey, you will need a JOIN with the candidates table:

SELECT SUM(tz.tot_votos) as total_votos, tc.*
    FROM tabela_zonas tz
    JOIN tabela_candidatos tc ON tc.id = tz.id_candidato
    WHERE ...
    GROUP BY tz.id_candidato

Answer 2

You can use SUM() to sum up the votes and GROUP BY to join the records by your candidate’s code.

SELECT SUM(TOT_VOTOS) FROM sua_tabela
WHERE id_candadito = $seu_id GROUP BY id;

You can recover information like name and related by adding JOIN. But at first the SQL above will give the result you want. But if it is not the expected result or need to modify or add something, please let us know!