Search code examples
mysqlcountinner-join

Combining SQL COUNT(*) with an INNER JOIN query

I've made a query to count and rank every row for a table, but I can't join the query into one table.

I've been trying to make a leaderboard and I've managed to make a query that ranks every row based on the amount of points they have (ie. 1st, 2nd, 3rd, etc). Due to my SQL version, I've had to use a workaround to ROW_NUMBER() and I'm struggling to include that query in this INNER JOIN query I've already got.

INNER JOIN query:

$query = mysqli_query($conn, "SELECT students.username, 
            CONCAT(spoints.points, 'pts') AS pointspts, 
            CONCAT(students.firstname, ' ', students.lastname) AS name, 
            CASE WHEN sex = 'M' THEN 'Male' ELSE 'Female' END AS sexes, 
            students.house, 
            CONCAT(students.age, 'yrs') AS ageyrs 
        FROM students 
            INNER JOIN spoints ON students.username=spoints.username 
        ORDER BY points DESC");

The COUNT(*) query in which I'm trying to merge into the INNER JOIN query:

$rank = mysqli_query($conn, "SELECT username, 1 + ( SELECT count(*) FROM spoints a WHERE a.points > b.points ) AS rank 
                        FROM spoints b 
                        ORDER BY rank;");

I'm trying to link these up so they'll form one succinct table, which is where the issue is arising.

I've tried a large variation of code joining them, such as:

$query = mysqli_query($conn, "SELECT students.username, 
            CONCAT(spoints.points, 'pts') AS pointspts, 
            CONCAT(students.firstname, ' ', students.lastname) AS name, 
            CASE WHEN sex = 'M' THEN 'Male' ELSE 'Female' END AS sexes, 
            students.house, CONCAT(students.age, 'yrs') AS ageyrs, 
            (SELECT 1 + (SELECT count(*) FROM spoints a WHERE a.points > b.points ) AS rank FROM spoints b) AS rank 
        FROM students 
            INNER JOIN spoints ON students.username=spoints.username 
        ORDER BY points DESC");

But that only seems to return a blank column or a "mysqli_query(): (21000/1242): Subquery returns more than 1 row" error.

I've also tried merging the queries with two mysqli_fetch_arrays but then I have this mutually exclusive problem in which only the first column of either the $rank query, or the $query query will print. I.e.

while ($roow = mysqli_fetch_array($rank)){
    while ($row = mysqli_fetch_array($query)){
        printf("
            <tr>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
            </tr>", $roow['rank'], $row['name'], $row['ageyrs'], $row['sexes'], $row['house'], $row['pointspts']
        ); 
    }
}

returns:

Standing Name Age Sex House Points
1st Kogan Spaghetti 17yrs Male Tasman 234pts
1st Ami Beckler 12yrs Female Pacific 24pts
1st Jan Schuette 18yrs Male Coral 0pts

OR

while ($row = mysqli_fetch_array($query)){
       while ($roow = mysqli_fetch_array($rank)){
        printf("
            <tr>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
            </tr>", $roow['rank'], $row['name'], $row['ageyrs'], $row['sexes'], $row['house'], $row['pointspts']
        ); 
    }
}

returns:

Standing Name Age Sex House Points
1st Kogan Spaghetti 17yrs Male Tasman 234pts
2nd Kogan Spaghetti 17yrs Male Tasman 234pts
3rd Kogan Spaghetti 17yrs Male Tasman 234pts

I want my code to return this:

Standing Name Age Sex House Points
1st Kogan Spaghetti 17yrs Male Tasman 234pts
2nd Ami Beckler 12yrs Female Pacific 24pts
3rd Jan Schuette 18yrs Male Coral 0pts

with the code I've already created which is clearly capable of doing so.

How do I merge these queries so they may all print together?

Solution

  • here's an example of how I believe it's a better way to get the expected data, I hope it helps.

    Edited in more detail for better understanding.

    select concat( s.firstname , ' ', s.lastname) as name,
    concat(s.age,'yrs') as Age ,
    CASE WHEN s.sex = 'M' THEN 'Male' ELSE 'Female' END AS Sex
    ,s.house  as House
# use sum(p.points) to count the number of points, 
# or count(p.points) if there is one # point per line 

,sum(p.points) as points

# Use the points table as the main one 
# and include the link with the left join of the students table
from spoints p

# I see that your secondary key link is by username,
# in my case I used p.student_id = s.id
# but in your case use
# p.username = s.username

    left join students s on p.student_id = s.id

# perform the grouping with reference to the username for your case
# group by p.username
group by p.student_id

# para obter os melhor colocados  
# utilize 'order by' para deixalos no top com ordenação 'desc'
order by points desc
# if your version has support use limit 3 to get only the first 3
limit 3

    Result

    name Age Sex House points
    Ami Beckler 12yrs Female Pacific 151
    Kogan Spaghetti 17yrs Male Tasman 141
    Jan Schuette 18yrs Male Coral 54

    sample database

    CREATE DATABASE teste;
use teste
create table students
(
    id        int auto_increment
        primary key,
    username  text null,
    firstname text null,
    lastname  text null,
    age       int  null,
    house     text null,
    sex       text null,
    constraint students_id_uindex
        unique (id)
);

INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('kogan.spaghetti', 'Kogan', 'Spaghetti', 17, 'Tasman ', 'M')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('ami.beckler', 'Ami', 'Beckler', 12, 'Pacific ', 'F')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('jan.schuette  ', 'Jan', 'Schuette', 18, 'Coral ', 'M')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('px.t1', 'px', 't1', 16, 'Pacific ', 'M')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('px.t2', 'px', 't2', 17, 'Pacific ', 'F')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('px.t3', 'px', 't3', 18, 'Pacific ', 'M')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('cx.t4', 'cx', 't4', 15, 'Coral ', 'M')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('cx.t5', 'cx', 't5', 14, 'Coral ', 'D')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('cx.t6', 'cx', 't6', 13, 'Coral ', 'M')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('tx.t7', 'tx', 't7', 13, 'Tasman ', 'M')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('tx.t8', 'tx', 't8', 13, 'Tasman ', 'M')
INSERT INTO teste.students (username, firstname, lastname, age, house, sex) VALUES ('tx.t9', 'tx', 't9', 13, 'Tasman ', 'M')

create table spoints
(
    id         int auto_increment
        primary key,
    username   text null,
    points     int  null,
    student_id int  null,
    constraint spoints_id_uindex
        unique (id),
    constraint spoints_students_id_fk
        foreign key (student_id) references students (id)
);


INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 2, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 5, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 2, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 9, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 6, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 6, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 1, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 3, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 8, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 7, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 2, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('jan.schuette  ', 3, 3)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('ami.beckler', 3, 2)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('ami.beckler', 12, 2)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('ami.beckler', 34, 2)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('ami.beckler', 5, 2)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('ami.beckler', 3, 2)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('ami.beckler', 6, 2)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('ami.beckler', 77, 2)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('ami.beckler', 8, 2)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('ami.beckler', 2, 2)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('ami.beckler', 1, 2)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 1, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 1, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 5, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 4, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 1, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 75, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 7, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 1, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 6, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 34, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 1, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('kogan.spaghetti', 5, 1)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('px.t1', 2, 4)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('px.t2', 2, 5)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('px.t3', 2, 6)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('cx.t4', 2, 7)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('cx.t5', 2, 8)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('cx.t6', 2, 9)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('tx.t7', 2, 10)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('tx.t8', 2, 11)
INSERT INTO teste.spoints (username, points, student_id) VALUES ('tx.t9', 2, 12)

    php exemple

    $i=0;
while ($row = mysqli_fetch_assoc($query)) {
$i++;
/*
use filter to add the st, nd, rd complement
using the variable $i as a line counter knowing that your query is already sorted
*/
$filter = array('th','st','nd','rd','th','th','th','th','th','th');
if (($i %100) >= 11 && ($i%100) <= 13)
   $rank = $i. 'th';
else
    $rank = ($i)? $i. $filter[$i % 10] : $i;

printf("
            <tr>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
            </tr>",$rank, $row['name'], $row['Age'], $row['Sex'], $row['House'], $row['points'].'pts'
        ); 
}

    diagram