Terminaxx Posted April 4, 2018 Share Posted April 4, 2018 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. Link to comment Share on other sites More sharing options...
requinix Posted April 4, 2018 Share Posted April 4, 2018 Use SUM on the value, a WHERE to restrict it to the types of records you want, and a GROUP BY on the user. Link to comment Share on other sites More sharing options...
Terminaxx Posted April 4, 2018 Author Share Posted April 4, 2018 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. Link to comment Share on other sites More sharing options...
Psycho Posted April 4, 2018 Share Posted April 4, 2018 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); Link to comment Share on other sites More sharing options...
Terminaxx Posted April 4, 2018 Author Share Posted April 4, 2018 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! Link to comment Share on other sites More sharing options...
Terminaxx Posted April 4, 2018 Author Share Posted April 4, 2018 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. Link to comment Share on other sites More sharing options...
Barand Posted April 4, 2018 Share Posted April 4, 2018 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 Link to comment Share on other sites More sharing options...
Terminaxx Posted April 4, 2018 Author Share Posted April 4, 2018 Wow. Thanks you two! Especially for not just giving me the answer but also for the good explanation! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.