I have results like that:All results And from results is Ranking list
query:
$results = $mysqli->query(" SELECT tv.*, (@rn := @rn + 1) as ranking FROM (SELECT liige_v.liige_id, liige_v.Eesnimi, liige_v.Perekonnanimi, punktid, SUM(punktid) AS punktidkokku FROM tulemus INNER JOIN liige_v ON tulemus.liige_id = liige_v.liige_id GROUP BY tulemus.liige_id ) tv CROSS JOIN (SELECT @rn := 0) vars ORDER BY punktidkokku desc; ");
table:
print '<table class="mytable4">'; echo "<tr><th>Koht </th><th>Liikme nimi </th><th> count results</th><th>Punktid</th></tr>"; while($row = $results->fetch_array()) { print '<tr>'; print '<td>' .$row["ranking"].'</td>'; print '<td>'.$row["Eesnimi"].' '.$row["Perekonnanimi"].'</td>'; print '<td>'.$row["countresults"].'</td>'; print '<td>'.$row["punktidkokku"].'</td>'; print '</tr>'; } print '</table>';
How I get “count results” as how many results give sum of points ? In my example first Aivar Narusson have 288 points from 4 races (count results = 4)
Advertisement
Answer
I think I understand the question. If you want the count, just include count(*)
in the aggregation:
SELECT tv.*, (@rn := @rn + 1) as ranking FROM (SELECT liige_v.liige_id, liige_v.Eesnimi, liige_v.Perekonnanimi, punktid, COUNT(*) as cnt, SUM(punktid) AS punktidkokku FROM tulemus INNER JOIN liige_v ON tulemus.liige_id = liige_v.liige_id GROUP BY tulemus.liige_id ) tv CROSS JOIN (SELECT @rn := 0) vars ORDER BY punktidkokku desc;