How to select all from MySQL table where column between dates and order by column

Question

I am working on a project to build an assessment leader board for a learning centre. I've got a table that looks like this

enter image description here

I am trying to write a query which will select all from the table by CLASS, within DATE range, add score per individual STUDENT_ID and then order in descending order by the added score to create the leader board. I've read a little on sub queries but can't quite understand the examples or exactly how they work, I also think I would need a SELECT DISTINCT student_id in my query but my knowledge here is also limited as I have only used it once.

Anyway this is what I have so far.

$classcheck = mysql_query("SELECT * 
                           FROM assessment 
                           WHERE class = '$class_info' 
                           order by score DESC") 
            or die(mysql_error());  

if(mysql_num_rows($classcheck) > 0){ 
    while($row = mysql_fetch_array($classcheck)){ 
        if(strtotime($row["date"]) > strtotime($fromdate) && strtotime($row["date"]) < strtotime($todate)){
            echo $row['score'].'<p>';
        }
    }
}

But I need it to add SCORE and order by the added SCORE in the query somewhere which I cannot achieve with what I have written.

I know should start using PDO rather than mysql_query, knowledge limited again and I am running out of time. All feedback would be greatly appreciated. OH, and the score is really a percentage.

Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 22 '15 at 20:13

Barmar · Accepted Answer · 2015-04-22T20:25:07.547

1

You don't need a subquery, you just need SUM and GROUP BY to total the scores by student, and a WHERE clause to restrict the dates.

SELECT student_name, SUM(score) AS total_score
FROM assessment
WHERE date BETWEEN '$fromdate' AND '$todate' 
    AND class = '$class_info'
GROUP BY student_id
ORDER BY total_score DESC

edited Apr 22 '15 at 20:25

answered Apr 22 '15 at 20:15

Barmar

741,623
53
500
612

score 0 · Answer 2 · answered Apr 22 '15 at 20:17

I think a GROUP BY might do the trick since you are trying to add up all the scores of an individual STUDENT_ID.

Please feel free to correct me if I'm wrong but the following SQL should get you what you are looking for.

SELECT SUM(score) AS ttl_score, student_name 
FROM assessment 
WHERE class='$class_info' 
AND date>='$start' AND date<='$end' 
GROUP BY student_id ORDER BY ttl_score DESC;

score 0 · Answer 3 · answered Apr 22 '15 at 20:20

$classcheck = mysql_query("Select Student_id, sum(Score) as SummedScore from      assessment where class='$class_info' and
date between '$fromdate' and '$todate' Group by Student_ID Order By SummedScore"
or die(mysql_error());

if(mysql_num_rows($classcheck) > 0){ 

 while($row = mysql_fetch_array($classcheck)){ 
     echo $row['SummedScore'].'<p>';}
}

How to select all from MySQL table where column between dates and order by column

3 Answers3