Skip to content
Advertisement

Count results rows

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;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement