How to SUM action from all users one by one?

[Terminaxx]

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.

[requinix]

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

[Terminaxx]

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.

[Psycho]

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

[Terminaxx]

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 April 4, 2018 by Terminaxx

[Terminaxx]

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 April 4, 2018 by Terminaxx

[Barand]

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

[Terminaxx]

Wow. Thanks you two! 

 

Especially for not just giving me the answer but also for the good explanation!