Combining SQL COUNT(*) with an INNER JOIN query – Mysql

If you made this a two stage process where the first stage is to generate a view of the data that accounts for the addition of scores then the second stage could use that view to then perform the rank calculation.

Borrowing slightly the table schema ( lightly modified ) from @Rafa Akito’s answer – viz:

CREATE TABLE `tbl_students` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` TEXT NULL,
    `firstname` TEXT NULL,
    `lastname` TEXT NULL,
    `age` INT(11) NULL DEFAULT NULL,
    `house` TEXT NULL,
    `sex` TEXT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `students_id_uindex` (`id`)
)
ENGINE=InnoDB;

CREATE TABLE `tbl_scores` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` TEXT NULL,
    `points` INT(11) NULL DEFAULT NULL,
    `student_id` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `scores_id_uindex` (`id`),
    INDEX `scores_students_id_fk` (`student_id`),
    CONSTRAINT `scores_students_id_fk` FOREIGN KEY (`student_id`) REFERENCES `tbl_students` (`id`)
)
ENGINE=InnoDB;

INSERT INTO `tbl_scores` (`id`, `username`, `points`, `student_id`) VALUES
    (1, 'jan.schuette', 2, 3),
    (2, 'jan.schuette', 5, 3),
    (3, 'jan.schuette', 2, 3),
    (4, 'jan.schuette', 9, 3),
    (5, 'jan.schuette', 6, 3),
    (6, 'jan.schuette', 6, 3),
    (7, 'jan.schuette', 1, 3),
    (8, 'jan.schuette', 3, 3),
    (9, 'jan.schuette', 8, 3),
    (10, 'jan.schuette', 7, 3),
    (11, 'jan.schuette', 2, 3),
    (12, 'jan.schuette', 3, 3),
    (13, 'ami.beckler', 3, 2),
    (14, 'ami.beckler', 12, 2),
    (15, 'ami.beckler', 34, 2),
    (16, 'ami.beckler', 5, 2),
    (17, 'ami.beckler', 3, 2),
    (18, 'ami.beckler', 6, 2),
    (19, 'ami.beckler', 77, 2),
    (20, 'ami.beckler', 8, 2),
    (21, 'ami.beckler', 2, 2),
    (22, 'ami.beckler', 1, 2),
    (23, 'kogan.spaghetti', 1, 1),
    (24, 'kogan.spaghetti', 1, 1),
    (25, 'kogan.spaghetti', 5, 1),
    (26, 'kogan.spaghetti', 4, 1),
    (27, 'kogan.spaghetti', 1, 1),
    (28, 'kogan.spaghetti', 75, 1),
    (29, 'kogan.spaghetti', 7, 1),
    (30, 'kogan.spaghetti', 1, 1),
    (31, 'kogan.spaghetti', 6, 1),
    (32, 'kogan.spaghetti', 34, 1),
    (33, 'kogan.spaghetti', 1, 1),
    (34, 'kogan.spaghetti', 5, 1),
    (35, 'px.t1', 2, 4),
    (36, 'px.t2', 2, 5),
    (37, 'px.t3', 2, 6),
    (38, 'cx.t4', 2, 7),
    (39, 'cx.t5', 2, 8),
    (40, 'cx.t6', 2, 9),
    (41, 'tx.t7', 2, 10),
    (42, 'tx.t8', 2, 11),
    (43, 'tx.t9', 2, 12);

INSERT INTO `tbl_students` (`id`, `username`, `firstname`, `lastname`, `age`, `house`, `sex`) VALUES
    (1, 'kogan.spaghetti', 'Kogan', 'Spaghetti', 17, 'Tasman ', 'M'),
    (2, 'ami.beckler', 'Ami', 'Beckler', 12, 'Pacific ', 'F'),
    (3, 'jan.schuette', 'Jan', 'Schuette', 18, 'Coral ', 'M'),
    (4, 'px.t1', 'px', 't1', 16, 'Pacific ', 'M'),
    (5, 'px.t2', 'px', 't2', 17, 'Pacific ', 'F'),
    (6, 'px.t3', 'px', 't3', 18, 'Pacific ', 'M'),
    (7, 'cx.t4', 'cx', 't4', 15, 'Coral ', 'M'),
    (8, 'cx.t5', 'cx', 't5', 14, 'Coral ', 'D'),
    (9, 'cx.t6', 'cx', 't6', 13, 'Coral ', 'M'),
    (10, 'tx.t7', 'tx', 't7', 13, 'Tasman ', 'M'),
    (11, 'tx.t8', 'tx', 't8', 13, 'Tasman ', 'M'),
    (12, 'tx.t9', 'tx', 't9', 13, 'Tasman ', 'M');

Then creating the view:

create view vw_stud_rank as
select 
    st.`username`, 
    sum( sc.`points` ) as `score`
from tbl_students st
    join tbl_scores sc on sc.`student_id`=st.`id`
group by st.`id`;

And finally the ranking calculation query:

select 
    s.*,
    case 
        when @pr = `score` then @cr 
        when @pr := `score` then @cr := @cr + 1
    end as `rank`
 from `vw_stud_rank` s,
 ( select @cr:=0,@pr:=null ) x
order by `score` desc;

The above query then yields:

+-----------------+-------+------+
| username        | score | rank |
+-----------------+-------+------+
| ami.beckler     |   151 |    1 |
| kogan.spaghetti |   141 |    2 |
| jan.schuette    |    54 |    3 |
| px.t1           |     2 |    4 |
| px.t2           |     2 |    4 |
| px.t3           |     2 |    4 |
| cx.t4           |     2 |    4 |
| cx.t5           |     2 |    4 |
| cx.t6           |     2 |    4 |
| tx.t7           |     2 |    4 |
| tx.t8           |     2 |    4 |
| tx.t9           |     2 |    4 |
+-----------------+-------+------+
12 rows in set (0.02 sec)

If you combine that with your own function ( found here ) – viz:

DELIMITER $$
CREATE FUNCTION ordinal(number BIGINT)
RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
  DECLARE ord VARCHAR(64);
  SET ord = (SELECT CONCAT(number, CASE
    WHEN number%100 BETWEEN 11 AND 13 THEN "th"
    WHEN number%10 = 1 THEN "st"
    WHEN number%10 = 2 THEN "nd"
    WHEN number%10 = 3 THEN "rd"
    ELSE "th"
  END));
  RETURN ord;
END$$
DELIMITER ;

You could then modify the above query to become:

select 
    s.*,
    case 
        when @pr = `score` then ordinal(@cr) 
        when @pr := `score` then @cr := ordinal(@cr + 1)
    end as `rank`
 from `vw_stud_rank` s,
 ( select @cr:=0, @pr:=null ) x
order by `score` desc;

Which itself yields:

+-----------------+-------+------+
| username        | score | rank |
+-----------------+-------+------+
| ami.beckler     |   151 | 1st  |
| kogan.spaghetti |   141 | 2nd  |
| jan.schuette    |    54 | 3rd  |
| px.t1           |     2 | 4th  |
| px.t2           |     2 | 4th  |
| px.t3           |     2 | 4th  |
| cx.t4           |     2 | 4th  |
| cx.t5           |     2 | 4th  |
| cx.t6           |     2 | 4th  |
| tx.t7           |     2 | 4th  |
| tx.t8           |     2 | 4th  |
| tx.t9           |     2 | 4th  |
+-----------------+-------+------+
12 rows in set (0.00 sec)

The view is simple to modify to include the yrs or Male/Female of course and it should be noted that later versions of mySQL – 8+ – have the rank() function built-in but the above was done within mySQL 5.5.8

A modified view:

create view vw_stud_rank as
select 
    st.`username`,
    concat(st.age,'yrs') as `age`,
    st.house,
    case
        when st.sex='m' then 'Male'
        when st.sex='f' then 'Female'
        else 'Non-Binary'
    end as `sex`,
    sum( sc.`points` ) as `score`
from tbl_students st
    join tbl_scores sc on sc.`student_id`=st.`id`
group by st.`id`;

That then yields the result more akin to the desired result:

+-----------------+-------+----------+------------+-------+------+
| username        | age   | house    | sex        | score | rank |
+-----------------+-------+----------+------------+-------+------+
| ami.beckler     | 12yrs | Pacific  | Female     |   151 | 1st  |
| kogan.spaghetti | 17yrs | Tasman   | Male       |   141 | 2nd  |
| jan.schuette    | 18yrs | Coral    | Male       |    54 | 3rd  |
| px.t1           | 16yrs | Pacific  | Male       |     2 | 4th  |
| px.t2           | 17yrs | Pacific  | Female     |     2 | 4th  |
| px.t3           | 18yrs | Pacific  | Male       |     2 | 4th  |
| cx.t4           | 15yrs | Coral    | Male       |     2 | 4th  |
| cx.t5           | 14yrs | Coral    | Non-Binary |     2 | 4th  |
| cx.t6           | 13yrs | Coral    | Male       |     2 | 4th  |
| tx.t7           | 13yrs | Tasman   | Male       |     2 | 4th  |
| tx.t8           | 13yrs | Tasman   | Male       |     2 | 4th  |
| tx.t9           | 13yrs | Tasman   | Male       |     2 | 4th  |
+-----------------+-------+----------+------------+-------+------+
12 rows in set (0.05 sec)

Which, when plugged into PHP, could yield final HTML as follows:

<?php
    
    chdir('../../dbo');
    require 'db-conn-details.php';
    require 'mysqli-conn.php';
    
    
    
    $sql="select
            case 
                when @pr = `score` then ordinal( @cr )
                when @pr := `score` then @cr := ordinal( @cr + 1 )
            end as `standing`,
            s.username,
            s.age,
            s.house,
            s.sex,
            concat( s.score, 'pts' ) as `points`

        from `vw_stud_rank` s,
         ( select @cr:=0,@pr:=null ) x
        order by `score` desc;";
?>
<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title></title>
        <style>
            table{
                border:1px solid grey;
                border-collapse:collapse;
                font-family:monospace;
                width:80%;
                float:none;
                margin:auto
            }
            tr th{
                background:grey;
                color:white;
            }
            td{
                border:1px dotted grey
            }
            th,td{
                padding:0.5rem;
                text-align:left;
            }
        </style>
    </head>
    <body>
        <table>
            <colgroup>
                <col width='5%' />
                <col width='55%' />
                <col width='10%' />
                <col width='10%' />
                <col width='10%' />
                <col width='10%' />
            </colgroup>
            <tr>
                <th>Standing</th>
                <th>Username</th>
                <th>Age</th>
                <th>House</th>
                <th>Gender</th>
                <th>Points</th>
            </tr>
        <?php
            $res=$db->query( $sql );
            while( $rs=$res->fetch_object() ){
                printf('
                    <tr>
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                    </tr>',
                    $rs->standing,
                    $rs->username,
                    $rs->age,
                    $rs->house,
                    $rs->sex,
                    $rs->points
                );
            }
        ?>
        </table>
    </body>
</html>