Count values with mysql queries
Let’s say you have the following table called bill that keeps track hours of people who are billable and non billable and you want a query to count the number of hours for you instead of having to loop through all the rows with a counter in PHP
(0= non bill hours , 1= bill hours)
| Work_id | Bill | Hours |
| 1 | 0 | 10 |
| 2 | 0 | 4 |
| 3 | 0 | 5 |
| 1 | 1 | 3 |
| 2 | 1 | 2 |
The sql to do this is
SELECT work_id , sum(IF(bill=1, (hours), 0)) AS Bill_hours, sum(IF(bill=0, (hours), 0)) AS Non_bill_hours FROM `work` GROUP BY work_id
| Work_id | Bill_hours | Non_bill_hours |
| 1 | 3 | 10 |
| 2 | 2 | 4 |
| 3 | 0 | 5 |
No comments:
Post a Comment