Need help creating a specific query

[israruval007]

I need help creating a specific query, The following is an example of my paycheck table, empId is a foreign key that refers to the primary key of my 'users' table which is 'userId'

mysql> SELECT * FROM paycheck
    -> ;
+------------+---------------+---------+-------------+-------------+-------------+-------+
| payCheckId | jobId         | payRate | jobLocation | hoursWorked | startPeriod | empId |
+------------+---------------+---------+-------------+-------------+-------------+-------+
|       1512 | entertainment |      12 | store1      |       10.00 | 2013-03-02  |     1 |
|       1510 | entertainment |      12 | store1      |        8.00 | 2013-03-01  |     1 |
|       1507 | retail        |      10 | store1      |        8.00 | 2013-03-18  |     1 |
|       1506 | retail        |      10 | store1      |       20.00 | 2013-03-19  |     1 |
+------------+---------------+---------+-------------+-------------+-------------+-------+

What i want is to calculate the sum of all hours for all specific jobId , in this case if i did the
query correctly it would look like this:

+---------------+---------------+---------+
| payID         | payRate       | hours   | 
+---------------+---------------+---------+
|  entertainment| 12            |      18 | 
|        retail | 10            |      28 | 
+---------------+---------------+---------+

In this case there is only two jobIds but it could have more than 2

This is the query i have and its only showing one payId, so I need help fixing it

also note that email is an attribute of my users table

<table>";
                 $query = "SELECT jobId,payRate,SUM(hoursWorked) AS 'All_Hours'
                          FROM users,paycheck
                          WHERE users.email = '" . $_SESSION['email'] .
                          "' AND userId = empId";  
                          $result = mysqli_query($db,$query);

                          if (!$result) { //if the query failed
                              echo("Error, the query could not be executed: " .
                              mysqli_error($db) . "</p>");
                              mysqli_close($db); //close the database
                          } //by now we have made a successful query  
                          while ($row = mysqli_fetch_assoc($result)){
                          echo "<tr><td>" .$row['jobId'] . "</td>
                                <td>" .$row['payRate'] . "</td>
                                <td>" .$row['All_Hours'] . "</td>
                                </tr>";
                          }
                  echo"</table>

[pixelsoul]

It maybe a silly question but how many results do you get back when you run that same exact query directly against your database, like in phpmyadmin?

[pixelsoul]

Nevermind my question, I see your problem and how the two need a join between them.

[pixelsoul]

You would need some type of join like this example:

SELECT
p.jobId,
p.payRate,
SUM(p.hoursWorked) AS 'All_Hours',
u.userid,
u.email

FROM users u JOIN paycheck p

ON p.empId = u.userId

WHERE u.email = '" . $_SESSION['email'] . "' AND u.userId = p.empId

Not positive that the above would work without testing but that is the basics of how it would be done.