Looping through sql table and calculate figures in php

Question

I have a what seems a simple question but I cannot workout how to do it.

Employee id | dev_time | pm_time
1           |    4     |    5
2           |    2     |    3
3           |    6     |    2
2           |    3     |    6
3           |    4     |    4
1           |    1     |    5

I have a table on my localhost that looks like this. How do I find out the total time (dev_time + pm_time) spent by each employee? I require both the SQL statement and the while/for/foreach loops solution

For example employee_id 1 has spent a total of 15hrs or employee_id 2 has spent a total of 14 hours

Thanks!!!!!

I have been using trying to use a foreach loop to loop through the table looking at each employee_id and trying to find the corresponding times. — user2133387, Mar 04 '13 at 21:49
@user2133387 You'll find that with SQL, it's much better to think of your queries in terms of _sets_ instead of trying to loop through your tables row by row. That's what you'll see in the answers below. — Jeff Rosenberg, Mar 04 '13 at 21:51

score 4 · Answer 1 · answered Mar 04 '13 at 21:44

4

Try this:

  select employee_id, sum(dev_time + pm_time) as totalhours
    from mytable
group by employee_id

Sample output (DEMO):

EMPLOYEE_ID | TOTALHOURS
1             15
2             14
3             16

answered Mar 04 '13 at 21:44

mellamokb

56,094
12
110
136

christopher · Answer 2 · 2013-03-04T22:32:19.313

3

I require both the SQL statement and the while/for/foreach loops solution

Well I have some bad news for you. Instead of straight up code, I'm going to give you some links so you can figure this out yourself.

First off, you want to look into basic SQL Operators. Then you'll see that the SQL statement you want to have the desired effect isn't all that difficult to deduce yourself.

Secondly, I'll give you the textual description:

Query all tuples from the database

Loop through each one, first grabbing their dev_time value, and summing it with their pm_time value.

Store the sum value somewhere

As mentioned by Conrad Friz, this textual description could lead to an "N+1 Selects Problem", and this can be done in a single statement, as you hopefully have worked out.

Hopefully you'll take this approach, rather than simply copying and pasting the code.

edited Mar 04 '13 at 22:32

answered Mar 04 '13 at 21:45

christopher

26,815
5
55
89

hmm, if you add a small requirement like *include only employees that have more > 2000 hours for 2012* the textual description of your solution could lead someone easily to a n + 1 selects problem. – Conrad Frix Mar 04 '13 at 22:26
Point made. Do you have a better suggestion? I would gladly edit it in :) – christopher Mar 04 '13 at 22:28
I wish I did. Textual models of sql statements are tricky. Perhaps just noting that it can be done in a single statement is enough. – Conrad Frix Mar 04 '13 at 22:31

score 2 · Answer 3 · answered Mar 04 '13 at 21:43

2

You can do this straight within the SQL, I'm not sure what DBMS you're using but in MySQL it's:

SELECT *, (dev_time+pm_time) AS total FROM table_name

answered Mar 04 '13 at 21:43

GManz

1,548
2
21
42

Looping through sql table and calculate figures in php

3 Answers3