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 Quote Link to comment 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? Quote Link to comment Share on other sites More sharing options...
sdbeach Posted August 20, 2012 Author Share Posted August 20, 2012 Sorry, just as an interger Quote Link to comment 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? Quote Link to comment 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 Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment 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 Quote Link to comment Share on other sites More sharing options...
Jessica Posted August 21, 2012 Share Posted August 21, 2012 Well then you're forgiven :-P Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.