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>