August 17, 2007 at 9:29 am
Dear experts
Does anyone know a method of 'rolling up' data using calculations such as median and interquartile ranges?
I know how to write code that will perform these calculations on the entire dataset, what I am interested in is how to get it to calculate values in a similar way to that in which an excel pivot table creates aggregated values.
In my example I have a source table containing 2 columns which I would like to group my data by (venue and date), and one column which is to have calculations run against it (los). If I were to do it manually I would select all the rows in my table which match the first 2 combinations in the group by field and then run my calculations against that dataset. This would create my first line of output. I would then move on to the second combination, and so on.
I would like to create a sql script that generates the following table (A) from the source data (B):
Required result (A):
Venue,Date,Min,LIR,Med,UIR,Max
Coombehaven,12/08/2007 00:00,3,12,18,37.5,297
Delderfield,05/08/2007 00:00,2,8.25,22.5,59,124
Delderfield,12/08/2007 00:00,2,12.5,39,70,131
SAMPLE DATA (B):
Venue,Date,los
Coombehaven,12/08/07,25
Coombehaven,12/08/07,16
Coombehaven,12/08/07,6
Coombehaven,12/08/07,87
Coombehaven,12/08/07,22
Coombehaven,12/08/07,12
Coombehaven,12/08/07,212
Coombehaven,12/08/07,297
Coombehaven,12/08/07,12
Coombehaven,12/08/07,39
Coombehaven,12/08/07,15
Coombehaven,12/08/07,9
Coombehaven,12/08/07,53
Coombehaven,12/08/07,19
Coombehaven,12/08/07,3
Coombehaven,12/08/07,13
Coombehaven,12/08/07,14
Coombehaven,12/08/07,3
Coombehaven,12/08/07,187
Coombehaven,12/08/07,53
Coombehaven,12/08/07,26
Coombehaven,12/08/07,26
Coombehaven,12/08/07,3
Coombehaven,12/08/07,121
Coombehaven,12/08/07,33
Coombehaven,12/08/07,19
Coombehaven,12/08/07,17
Coombehaven,12/08/07,13
Coombehaven,12/08/07,9
Coombehaven,12/08/07,8
Delderfield,05/08/07,13
Delderfield,05/08/07,37
Delderfield,05/08/07,5
Delderfield,05/08/07,81
Delderfield,05/08/07,67
Delderfield,05/08/07,3
Delderfield,05/08/07,42
Delderfield,05/08/07,8
Delderfield,05/08/07,124
Delderfield,05/08/07,59
Delderfield,05/08/07,32
Delderfield,05/08/07,2
Delderfield,05/08/07,10
Delderfield,05/08/07,84
Delderfield,05/08/07,88
Delderfield,05/08/07,48
Delderfield,05/08/07,59
Delderfield,05/08/07,13
Delderfield,05/08/07,9
Delderfield,05/08/07,8
Delderfield,05/08/07,6
Delderfield,05/08/07,11
Delderfield,12/08/07,20
Delderfield,12/08/07,2
Delderfield,12/08/07,88
Delderfield,12/08/07,74
Delderfield,12/08/07,10
Delderfield,12/08/07,3
Delderfield,12/08/07,49
Delderfield,12/08/07,131
Delderfield,12/08/07,66
Delderfield,12/08/07,39
Delderfield,12/08/07,9
Delderfield,12/08/07,17
Delderfield,12/08/07,5
Delderfield,12/08/07,91
Delderfield,12/08/07,95
Delderfield,12/08/07,55
Delderfield,12/08/07,66
Delderfield,12/08/07,16
Delderfield,12/08/07,15
Many thanks
Thomas Bartlett
August 17, 2007 at 9:43 am
You can do things like
select venue, date, avg(los)
from table
group by venue, date
Are the dates really dates only or are times in there. You might be able to clean them down to the date only, which would make this easier. You can replace the avg() with sum(), sum()/count(), etc.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply