Group Sum Per Day of Week and Put in Array

Question

I have a table which stores labor data. One of the columns is the amount of hours worked (rthours) on a specific project, another column is the date (labor_date).

I need to grab the sum of all hours worked for each day of the current week and put them into an array to use with a charting script which generates a bar chart.

The chart starts on Monday and ends on Sunday.

I have the following select statement written but it returns wrong values, it also does not take into consideration if there has not been any hours recorded for one of the days.

Any suggestions?

Here is the code:

$sql = "SELECT SUM(rthours) as total FROM data WHERE (WEEK(labor_date) = WEEK(NOW())) AND (user_name = '$user') GROUP BY DAY(labor_date) ORDER BY DAY(labor_date)";

$result = mysqli_query($conn, $sql);
$thours = array();

while ($row = mysqli_fetch_assoc($result)) {
           $thours[] = $row["total"];
        }
mysqli_close($conn);

?>

Inside the while loop, add something like j=0 then iterate by 1 and use $thours[j] = $row['total']; — user3741598, Feb 17 '17 at 20:15
why are there 2 answers from you below? which one's the right one? — Funk Forty Niner, Apr 19 '17 at 15:09

score 1 · Answer 1 · answered Feb 17 '17 at 20:20

First of all, your code is open to SQL injection. I will not address this here, but please fix it.

You need to use the function weekday (1 - 7), not day (1 - 31), since the latter could cross a month boundary and then produce the wrong order.

To fill the gaps, you could add a virtual table to your query which will produce the numbers 1 to 7, and then outer join your data with that. For missing days your sum will then still be included, but null. With coalesce you can turn that into value 0:

SELECT    COALESCE(SUM(rthours), 0) as total 
FROM      (SELECT 1 as weekday
             UNION ALL SELECT 2 UNION ALL SELECT 3 
             UNION ALL SELECT 4 UNION ALL SELECT 5 
             UNION ALL SELECT 6 UNION ALL SELECT 7) as ref
LEFT JOIN data 
       ON DAYOFWEEK(labor_date) = ref.weekday
WHERE     WEEK(labor_date) = WEEK(NOW())
AND       user_name = ?
GROUP BY  ref.weekday 
ORDER BY  ref.weekday

Hello, thank you for your help. This does not work. The chart gets populated with the number 8 anywhere data is missing for a day of the week. One employee has 8 hours recorded for Monday but no data yet for the rest of the week, yet the chart shows 8 hours for Monday through Friday, — Lawrence P., Feb 21 '17 at 16:05

score 0 · Answer 2 · edited Feb 17 '17 at 22:17

0

Figured it out. Using this select statement works:

SELECT SUM(rthours) total
  FROM data 
 WHERE (WEEK(labor_date) = WEEK(NOW())) 
   AND (YEAR(labor_date) = YEAR(NOW())) 
   AND (user_name = '$user') 
 GROUP 
    BY WEEKDAY(labor_date) 
 ORDER 
    BY WEEKDAY(labor_date);

edited Feb 17 '17 at 22:17

Strawberry

33,750
13
40
57

answered Feb 17 '17 at 20:19

Lawrence P.

47
6

... but it does not solve the second part of your question: filling the gaps. See my answer. – trincot Feb 17 '17 at 20:21
How do you know which total is which? – Strawberry Feb 17 '17 at 22:19

score 0 · Answer 3 · answered Feb 24 '17 at 14:54

Edited code to this:

"SELECT COALESCE(SUM(rthours) + SUM(othours) + SUM(trthours) + SUM(tothours), 0) as total
FROM (SELECT 1 as weekday
             UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5
             UNION ALL SELECT 6 UNION ALL SELECT 7) as ref
LEFT JOIN data
       ON DAYOFWEEK(labor_date) = ref.weekday
WHERE     WEEK(labor_date) = WEEK(NOW()) AND Year(labor_date) = Year(NOW())
AND       user_name = '$user'
GROUP BY  ref.weekday
ORDER BY  ref.weekday";

The code is grabbing the data but is still not placing zeros when data does not exist for a certain day.

Group Sum Per Day of Week and Put in Array

3 Answers3