CODEWITHSUNDEEP
phpmysqlsqljoin
Kaloyan
Kaloyan

Reputation: 41

PHP MySql Query Get Sum Per User

I have users with rating and I want to show the total amount of rating of each user. I have 2 tables: users and feedback. I have 4 users in USERS table. And in table FEEDBACK (consists of applicant_id and app_experience columns) I have the rating of each user. But, I have several ratings for each person.

  $sql4 = "
 SELECT SUM(app_experience) as app_experience
     , applicant_id 
  FROM feedback f
  JOIN users u
    ON u.id=f.applicant_id 
 GROUP 
    BY feedback.applicant_id
";
  $res4 = mysqli_query($db,$sql4) or die(mysqli_error($db));

This is my output, but it prints only 2 users, because in table FEEDBACK 2 of the users do not have any feedback yet:

  foreach ($res4 as $row) 
  {
   echo "".$row['applicant_id']."----".$row['app_experience']."";
   echo "<br>";
  }

My question is how to output all 4 users and I want to show the total number of rating next to each user.

I am doing something like this, but I do not know where to add the foreach loop above in the code below. Do you have any ideas?:

 $sql2 = "SELECT * FROM users";
 $res2 = mysqli_query($db,$sql2) or die(mysqli_error($db));
 while($row2 = mysqli_fetch_assoc($res2))
 {
   $id = $row2['id'];
   $name = $row2['name'];
 }

Upvotes: 3

Views: 1280

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269893

As written, your query will not run. The GROUP BY clause is referencing feedback, but that reference has been changed to f by the alias.

You appear to want a LEFT JOIN:

SELECT u.id, SUM(f.app_experience) as app_experience
FROM feedback f LEFT JOIN
     users u
     ON u.id = f.applicant_id 
GROUP BY u.id;

This will return a NULL value for app_experience. If you want a value (such as 0) use COALESCE(SUM(f.app_experience), 0).

Upvotes: 0

GMB
GMB

Reputation: 222482

You just have to modify the below statement :

$sql4 = "SELECT SUM(app_experience) as app_experience, applicant_id FROM feedback INNER JOIN users ON users.id=feedback.applicant_id GROUP BY feedback.applicant_id";

to :

$sql4 = "SELECT COALESCE(SUM(app_experience), 0) as app_experience, applicant_id FROM users LEFT JOIN feedback ON users.id=feedback.applicant_id GROUP BY users.id";

The users LEFT JOIN feedback ... clause will allow the query to return a row even for users who do not have a feedback yet.

The COALESCE(SUM(app_experience), 0) will evaluate to 0 when a user has no feedback yet.

With this solution you don't need to loop.

Upvotes: 1

Patrick
Patrick

Reputation: 313

SELECT users.*, (SELECT SUM(app_experience) FROM feedback WHERE applicant_id = users.id) as feedback FROM users

Selects all users and their feedback in a short SQL

Upvotes: 0

Related Questions