Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Adding a min, max and avg column Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, March 18, 2013 4:11 PM
 Forum Newbie Group: General Forum Members Last Login: Friday, September 18, 2015 2:56 AM Points: 6, Visits: 96
 I have a table like so.Table A [Job Lot] [Job ID] [Child Job ID] [Time Completed] [Order of Jobs Done] 1 11 100 10 Pos 1 1 11 101 20 Pos 2 1 11 103 30 Pos 3 2 12 101 30 Pos 1 2 12 105 10 Pos 3Each Child Job ID represents a delivery to a particular site like New York, Washington or LA each week.So I need to work work out say for Child ID 101 what is the average postion . I see in JOB ID 11 it has Child Job ID 100 in POS 2 and in JOB ID 12 it is in POS 1, so the min is POS1 , the max is POS2, and the average is POS 1.5. So what is the spread of CHILD JOB ID and position is the real question , so out of 4 CHILD JOB IDS of say 101 what is the spread, is it always usuallt at postion 1.For CHILD JOB ID 10 , I want the lowest rank of Position 0 , the highest rank value of Position 3 and the Average vlaue not considering any null values and only divide by number of non null vlaues. In this case the the average rank would be Postion 2.So I need to add 3 columns to store these values ? How can I do this in one query ? Do I need to pivot the data first ?SELECT [JOB LOT],[CHILD JOB ID], MIN([ORDER OF JOBS DONE])AS [MIN],MAX([ORDE OF JOBS DONE])AS [MAX] FROM TABLE A WHERE [ORDER OF JOBS DONE] IS NOT NULL group by [JOB LOT],[ORDER OF JOBS DONE]
Post #1432378
 Posted Monday, March 18, 2013 4:56 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:09 PM Points: 42,046, Visits: 39,429