Jump to content
Terminaxx

How to SUM action from all users one by one?

Recommended Posts

Hey guys,

 

 

I got a table where transactions are listed in, therefore we got once the "receiver", the "comment" and the "action" (amount)
For exmaple

User A - something - 50

User B - something - 50

User C - tax - 100

User D - tax - 100

User D - tax - 200

User F- tax - 200

 

I want to get the sum from every action with the comment "tax" listed by users

So it should be like this:

User C - tax - 100

User D - tax - 300  <-- 200 + 100

User F - tax - 200

 

How can I do this?

 

 

Thanks for any help and I hope it is clear, what I am trying to do.

Share this post


Link to post
Share on other sites

Use SUM on the value, a WHERE to restrict it to the types of records you want, and a GROUP BY on the user.

Share this post


Link to post
Share on other sites

Sorry but I don't really get it. I tried this:

$result = mysqli_query($con, "SELECT SUM(action) AS value_sum FROM transactions WHERE comment = 'tax' ORDER BY receiver"); 

Did you mean this? Or how exactly am I gonna do this? Sorry but im kinda new to this.

Share this post


Link to post
Share on other sites

1. You are only SELECTing the amount, but you also need the receiver in your data. So, that needs to be in the SELECT condition

2. The SUM() function requires a GROUP BY - this determine which records get summed. Since you want all the 'actions' added for each receiver, you need to GROUP BY receiver

 

3. I would highly suggest creating your queries as a variable and including the variable in the query functions instead of writing the queries directly in the query function. It makes management and debugging much, much easier. And format the query to help in reading it.

 

 

$query = "SELECT receiver, SUM(action) as value_sum
          FROM transactions 
          WHERE comment = 'tax'
          GROUP BY receiver
          ORDER BY receiver";
$result = mysqli_query($con, $query);

Share this post


Link to post
Share on other sites

Hey, thanks for your help it sound really logical. 

 

 

I also would like to thank you for your third hint. Going to consider it when I code again!

 

 

 

 

 

Thanks again.

 

 

Edit: Got it!

Edited by Terminaxx

Share this post


Link to post
Share on other sites

Edit: 

 

Sorry to make it sound stupid again, but how exactly can I echo the sum correctly now?

I tried this:

while($row = mysqli_fetch_object($res)) {
$name = $row->receiver;
$row = mysqli_fetch_assoc($res);
$sum = $row['value_sum'];

echo "<tr>";
echo "<td>".$name."</td>";
echo "<td>".number_format($sum, 0, ",", ".")." Money</td>";
}

Sorry if it is really easy for you and it annoys you, but I still try to learn and understand.

 

 

 

It shows some results, but they are way too low so they can't be correct.

Edited by Terminaxx

Share this post


Link to post
Share on other sites

Each iteration of the while() loop fetches a row. Within that loop you then fetch another row (for some reason). So now each pass throught the loop is reading two rows.

Remove The line with fetch_assoc() and use $sum = $row->value_sum; as you have chosen to fetch objects

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.