Group Results & Count Values In While Loop

Question

I have a simple query that lists the employees name, the hours/minutes they have worked and the cost for those hours. Here is the query, it joins 3 tables:

$usersResult = mysqli_query($con,"SELECT a.UF_TIME_ID, a.UF_USER, a.UF_COST,
b.ID, b.NAME, b.LAST_NAME, c.ID, c.MINUTES, c.TASK_ID FROM time_cost a INNER
JOIN b_user b ON a.UF_USER = b.ID INNER JOIN b_tasks_elapsed_time c ON 
a.UF_TIME_ID = c.ID GROUP BY a.UF_USER");
while($row = mysqli_fetch_array($usersResult)

The minutes field is simply will read 120 for the number of minutes. I then have a function that splits this into hours and minutes. From this in the loop I get a table that looks like:

NAME          | Time Spent    | Cost
Bob           | 2h 30m        | 50
Bob           | 2h 15m        | 40
Dave          | 3h 30m        | 120
Bob           | 1h 15m        | 20
Dave          | 4h 30m        | 180

Now I want to group them, I have tried to GROUP BY a.UF_USER but it doesn't add the time or costs up correctly which I can understand. I have then tried:

SUM(c.MINUTES) AS TOTAL

And used that with and without the GROUP but that also doesn't add the values up correctly. I have also tried:

$totalTime += $row['MINUTES'];

But again with grouping it doesn't display. Here is the code after the while loop I have:

   {
   $userFirstName = $row['NAME'];
   $userLastName = $row['LAST_NAME'];
   $userCost = $row['UF_COST'];
   $userMinutes = $row['MINUTES'];


   $userTime = $userMinutes;
   $userTime = convertFromMinutes($userTime);


   $totaluserHours = $userTime['hours'];
   $totaluserMinutes = $userTime['minutes'];

   print "<tr><td>$userFirstName $userLastName</td><td>$totaluserHours H $totaluserMinutes M</td><td>$userCost</td></tr>"; 
   }

I have a function that counts the minutes and splits into hours and minutes. So I need the end outcome to basically read:

NAME          | Time Spent    | Cost
Bob           | 6h 00m        | 110
Dave          | 8h 00m        | 300

Your help would greatly be appreciated. I'm sure it is simple and I'm missing the point here.

You should describe what is in the hours and minutes fields (give us some sample data) and then provide us with expected output and the output you get when you tried the various solutions. — Shadow
– Shadow, Commented Nov 12, 2015 at 12:14

ScaisEdge · Accepted Answer · 2015-11-12 12:22:13Z

1

for get ths add you need a sum

try this way

$usersResult = mysqli_query($con,"SELECT a.UF_TIME_ID, a.UF_USER, sum(a.UF_COST),
b.ID, b.NAME, b.LAST_NAME, c.ID, sum(c.MINUTES), c.TASK_ID FROM time_cost a  INNER
JOIN b_user b ON a.UF_USER = b.ID INNER JOIN b_tasks_elapsed_time c ON 
 a.UF_TIME_ID = c.ID GROUP BY a.UF_USER");

answered Nov 12, 2015 at 12:22

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

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

1 Comment

andy Over a year ago

Wow, I was not using sum on both so it wasn't adding one and excluding the other. I knew it was a simple solution I was missing.

Collectives™ on Stack Overflow

Group Results & Count Values In While Loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related