CODEWITHSUNDEEP
phphtmlmysqlsql
Jan Leon
Jan Leon

Reputation: 176

Sum query not working properly properly sql and php

Why this query instead of displaying the sum of points for each user, it display the sum of all together

I have written an SQL query that displays the sum of all point for all users, whereas I would like it to: display the sum of points for each user.

The table that I have written contains the following:

id  |  Score
1   |   20
2   |   30
2   |   50
1   |   10

Total table :

id | points

1  | 30

1  | 40

What I want is to add the score for user(1) = 30 and user(2) = 80

Id: 1 = 30 = Fail
Id: 2 = 80 = Pass

The query I have written :

$query = "SELECT SUM(sg.score) as sum, SUM(a.total) as suma FROM points as sg, totalpoints as a
          WHERE a.id = 1 GROUP BY sg.id";

And related PHP code is as follows:

<?php
  foreach($rowsum as $rowsm): 
    if( ' '. htmlentities($rowsm['sum']) . '' > 50  )
      echo 'Pass';
    else if( ' '. htmlentities($rowsm['sum']) . '' >= 40  )
      echo 'Failed';

    echo ' ' . htmlentities($rowsm['sum'], ENT_QUOTES, 'UTF-8') . '<br>';

  endforeach;
?>

Upvotes: 1

Views: 2087

Answers (4)

potashin
potashin

Reputation: 44581

I guess you should look forward using the GROUP BY clause :

SELECT 
   SUM(score) AS sum 
FROM 
   points 
 GROUP BY 
   id 
 ORDER BY 
   id

Upvotes: 1

Jonathan
Jonathan

Reputation: 1564

You need to group by the users ID:

SELECT SUM(score) as sum FROM points GROUP BY id ORDER BY id

You also have an incorrect WHERE clause

WHERE id=id

isn't needed.

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You need to do group by as below and it will give you the sum of scores for each user

SELECT SUM(score) as sum FROM points 
group by id 
ORDER BY id

If you need to find it for a specific user just add a where condition as

SELECT SUM(score) as sum FROM points 
where id = 1

The above will give the sum of scores for id = 1 and can change for other values as needed.

Upvotes: 0

VMai
VMai

Reputation: 10336

You've omitted the GROUP BY clause:

$query = "SELECT SUM(score) as `sum` FROM points GROUP BY id ORDER BY id";

Your WHERE clause wasn't needed.

Upvotes: 0

Related Questions