Hope you all having a good time coding, but I’m stuck with this sql query. Scenario is that i have a simple employee’s database. The data is saved into mysql table. the table has 35 columns. each column corresponds to month date. and i am trying to pick work hours entered in each column for everyone. 35 columns should combine to sum up into total work hours of month for a particular person. and my query isn’t working. it aggregates sum of all columns according to where clause for all the workers and shows it as single result. whereas my requirement is to calculate work hours for each worker separately.
Also please note that i am aware of sql injection and input sanitation shorter column count but here it is not required as this app is for local use only.
i tried sql query with ‘where’ having three criteria for record selection that is: select against specific month, year and work_grade for particular person.
$sql = "SELECT staff_id, `worker_name`, `work_grade`, `m`, `y`,`1`,`2`, sum(`1`+`2`) as `totalhours` FROM tbl_s where `work_grade` ='$_GET[work_grade]' and `m` ='$_GET[month]' and `y` ='$_GET[year]' order by ts_id desc";
The result should be like sum of day 1 of month through last day of month 30 or 31st.
Such as sum of dates: 1+2+3+4+5…..31 = 310 supposing if worker worked for 10 hours daily.
In my sql query here i have shown only 2 columns just to reduce length of sentence for better understanding.
i have already searched numerous seemingly almost similar but could no find either proper details or the answers aren’t comprehensive enough for understanding.
Hoping I will get a solution or an advise.
Thanks!