Mysql : How to Select SUM of a column based of a condition value from another table?

Question

So, I have this scenario of a quiz where questions are attached to groups and so when someone do a quiz, first an attempt is stored in a table and then as person does quiz e.g of 3 questions, its points are recorded with points, question, group and quiz attempt id in a table. Here are my table structure:

Table attempts

id | status ( 0 for not complete, 1 for complete )
1  | 1
2  | 1
3  | 0

Table groups

id | title
1  | Group 1
2  | Group 2
3  | Group 3

Table points

id | attempt_id | group_id | points
1  | 1          | 2        | 5
2  | 1          | 2        | 5
3  | 1          | 1        | 5
4  | 2          | 3        | 5
5  | 2          | 2        | 5
6  | 2          | 3        | 5
7  | 3          | 1        | 5
8  | 3          | 3        | 5

I need to show the group list with sum of each group from points table infront of them but only from the completed attempts from attempts table:

Here's how I am trying:

$query = mysqli_query($conn, "SELECT * FROM `groups` order BY `id` ASC");
while ( $row = mysqli_fetch_array($query) ) {
 $group_id = $row["id"];
 $gpc = mysqli_query($conn, "SELECT SUM(`points`) as TotalPoints FROM `points` WHERE `attempt_id` IN (SELECT `id` FROM `attempts` WHERE `status`=1) AND `group_id`=".$group_id);
 $gpcrow = mysqli_fetch_assoc($gpc);
 echo $row["title"]." : ".$gpcrow["TotalPoints"];
}

Result should be:

Group 1 : 5
Group 2 : 15
Group 3 : 10

But its not giving me exact results as its giving me results from all complete and incomplete attempts. But I need results only from completed attempts in group listing shape.

[Edit] the question and post the desired result with the sample data you gave, i.e. complete the [example]. — sticky bit, Jul 07 '21 at 15:11
(Possible) side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Jul 07 '21 at 15:12
your query works as intended see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=341c09e2d1e0fbe24d32eff33f8dbfa0 — nbk, Jul 07 '21 at 15:24
@SamCh: Hmm you're making two mistakes at the moment. a) It *is* necessary (maybe not for you, as it's *your* problem and of course you know and understand it well, but for *us*, to be on the same page and able to help *you* as best as possible. You should really change your attitude and shift the focus to your audience), so it's good that you did so. And b) I didn't vote yet (not up, not down) as it was only that little detail that was missing and I wanted to give you the chance to correct it. — sticky bit, Jul 07 '21 at 15:26
I can't reproduce this https://www.db-fiddle.com/f/r9r2axf8dFC3vZaVSMMRLy/0 — Dharman, Jul 07 '21 at 15:26
I don't think your question has anything to do with PHP or mysqli, so adding these details into the question is unnecessary. — Dharman, Jul 07 '21 at 15:28
If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo & https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection — Dharman, Jul 07 '21 at 15:28

nbk · Answer 1 · 2021-07-07T16:52:43.407

You don't need the loop at all, or in this case at least prepared statements

<?php

if (
    $result = mysqli_query(
        $mysqli,
        "SELECT  MIN(g.title) title,SUM(points)
FROM points p INNER JOIN `groups` g ON p.group_id = g.id
WHERE `attempt_id`IN (SELECT id FROM attempts WHERE status = 1) GROUP BY  `group_id`"
    )
) {
    // Fetch all
    $gpcrow = mysqli_fetch_all($result, MYSQLI_ASSOC);
    print_r($gpcrow);
    // Free result set

    // Free result set
    mysqli_free_result($result);
}
?>

See example

Mysql : How to Select SUM of a column based of a condition value from another table?

1 Answers1