sdbeach Posted August 20, 2012 Share Posted August 20, 2012 I am trying to sort by date. The date returns 'job numbers' that contain information about how many hours was worked on each job. I would like to be able to sum all of the hours worked by the job number. The job number contains varying amounts of columns depending on what job is picked. The date is picked from a date picker, then I would like the results to appear like below. So: | job | date | a_hrs | b_hrs | c_hrs | --------------------------------------------------------------- |job1| 8\8\12 | 3 hr | 6hrs | 2hrs | |job1| 8\8\12 | 2 hr | 3hrs | 4hrs | |job1| 8\8\12 | 3 hr | 4hrs | 5hrs | |job2| 8\8\12 | 3 hr | 6hrs | 2hrs | |job1| 8\8\12 | 3 hr | 6hrs | 2hrs | |job2| 8\8\12 | 3 hr | 6hrs | 2hrs | Result I desire: | job1 | 8\8\12 | 11 hr | 19hrs | 13hrs | | job2 | 8\8\12 | 6 hr | 12hrs | 4hrs | Thanks in advance for any help. Scott Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/ Share on other sites More sharing options...
Jessica Posted August 20, 2012 Share Posted August 20, 2012 Are they stored as an INT or as a string with " hr" on the end? Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/#findComment-1370925 Share on other sites More sharing options...
sdbeach Posted August 20, 2012 Author Share Posted August 20, 2012 Sorry, just as an interger Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/#findComment-1370927 Share on other sites More sharing options...
requinix Posted August 20, 2012 Share Posted August 20, 2012 The job number contains varying amounts of columns depending on what job is picked. Can you elaborate on that very scary statement? Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/#findComment-1370933 Share on other sites More sharing options...
Jessica Posted August 20, 2012 Share Posted August 20, 2012 SELECT job_id, date, SUM(a_hrs), SUM(b_hrs), SUM(c_hrs) FROM jobs GROUP BY job_id, date Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/#findComment-1370937 Share on other sites More sharing options...
sdbeach Posted August 20, 2012 Author Share Posted August 20, 2012 I meant that some jobs may contain 5 rows of data, and some jobs may contain 3 rows. It all depends on what day had what category of work done. See my example up top: Job 1 has 4 rows to sum job 2 has 2 rows to sum. Scott Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/#findComment-1370938 Share on other sites More sharing options...
Jessica Posted August 20, 2012 Share Posted August 20, 2012 I assume you mean columns? Because it doesn't matter how many rows it is, SUM adds them. If you mean columns, your example does not show different numbers of columns. Further more, given the test data, what I posted creates your desired output. Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/#findComment-1370939 Share on other sites More sharing options...
sdbeach Posted August 20, 2012 Author Share Posted August 20, 2012 jesirose, Thanks for your help. It worked perfectly! It is great that some one as new to SQL as me can get help from the pros! Scott Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/#findComment-1370943 Share on other sites More sharing options...
Jessica Posted August 20, 2012 Share Posted August 20, 2012 You're welcome. In the future before telling someone they've given you the wrong solution, try it out. Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/#findComment-1370954 Share on other sites More sharing options...
sdbeach Posted August 20, 2012 Author Share Posted August 20, 2012 That comment was for "requinix" about looking at my example. But thanks again for the solution! Scott Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/#findComment-1370981 Share on other sites More sharing options...
Jessica Posted August 21, 2012 Share Posted August 21, 2012 Well then you're forgiven :-P Link to comment https://forums.phpfreaks.com/topic/267350-sum-of-columns-from-multiple-varying-rows/#findComment-1371073 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.