creating a specific query using mysql and php

Question

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

  mysql> SELECT * FROM deposit
    -> ;
+------------+---------------+---------+-------------+-------------+-------------+-------+
|    CheckId | jobId         | payRate | jobLocation | hours       | date_paid   | 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(hours) AS 'All_Hours'
                       FROM users INNER JOIN deposit ON userId = empId
                      WHERE users.email = '" . $_SESSION['email'] ."' 
                      GROUP BY jobId,payRate";

                      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>

John Woo · Accepted Answer · 2013-03-20 03:18:07Z

3

you forgot to add GROUP BY clause in your query causing to have only one record in the result,

SELECT jobId, payRate, SUM(hoursWorked) AS 'All_Hours'
FROM   users INNER JOIN paycheck ON userId = empId
WHERE  users.email = 'session_email_here' 
GROUP  BY jobId, payRate

answered Mar 20, 2013 at 3:18

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

2 Comments

user2189151 Over a year ago

i edited my query and now its not showing anything, is there anything wrong the way i edited?

user2189151 Over a year ago

nvm i ran it on phpmyadmin and it worked but there is a syntax error somewhere on my code

Andrew · Accepted Answer · 2013-03-20 03:26:15Z

0

On the deposit table

SELECT jobId, payRate, sum(hours) FROM deposit
group by 1, 2

I would also suggest that you make it little bit more relational: jobId and jobLocation needs to be moved to new tables

answered Mar 20, 2013 at 3:26

Andrew

7,82813 gold badges68 silver badges120 bronze badges

Collectives™ on Stack Overflow

creating a specific query using mysql and php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related