Sum query not working properly properly sql and php

Question

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;
?>

Jonathan · Accepted Answer · 2014-04-27 21:57:33Z

1

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.

answered Apr 27, 2014 at 21:57

Jonathan

1,5643 gold badges16 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

souslov · Accepted Answer · 2014-04-27 22:04:47Z

1

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

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

edited Apr 27, 2014 at 22:04

answered Apr 27, 2014 at 21:56

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Comments

VMai · Accepted Answer · 2014-04-27 21:56:26Z

0

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.

answered Apr 27, 2014 at 21:56

VMai

10.4k9 gold badges28 silver badges36 bronze badges

2 Comments

Jan Leon Over a year ago

Thank you :), and It is posible to first sum all the values and then do the sum for id

VMai Over a year ago

If you want the total sum included you can do it with a subselect:

Abhik Chakraborty · Accepted Answer · 2014-04-27 21:56:31Z

0

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.

answered Apr 27, 2014 at 21:56

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

4 Comments

Jan Leon Over a year ago

I updated my question, hope you can help is a diff query

Abhik Chakraborty Over a year ago

In this case you need to use the first query and will return exactly what you want.

Jan Leon Over a year ago

My problem is that total point, Im getting it from a different table

Abhik Chakraborty Over a year ago

different table ? you may need to add the table and data in that case to your question.

Collectives™ on Stack Overflow

Sum query not working properly properly sql and php

4 Answers 4

Comments

Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related