Quartiles

Question

Post reply

Quartiles

Thomas Bartlett

SSC Enthusiast

Points: 182
More actions
August 13, 2007 at 9:22 am

#118118

Dear experts
I have a table that has three fields: a) venue, b) date, c) duration of attendance in days.
The no of records in this table is determined by the no of venues in my organisation (30) multiplied by an ever increasing no of dates multiplied by the no of attendees on each date (around 20 per venue). Currently it contains 34,000 records.
I want to calculate:
1. the minimum of c)
2. the lower interquartile range of c)
3. the median of c)
4. the upper interquartile range of c)
5. the maximum of c)
and group these figures by columns a) and b).
i.e. the fields in the output table would be: a), b), 1, 2, 3, 4, 5
Many thanks
Thomas Bartlett

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

We'd need the table DDL, sample data and required output from that data to help you here.

bledu SSChampion Points: 12335 More actions · Answer 2

we need more info.

but out of curiosity why do you want to do it in sql, and not a frontend program?

nonetheless bring it on

Everything you can imagine is real.

bledu SSChampion Points: 12335 More actions · Answer 3

1.

SELECT venue,fdate

FROM TABLE1

WHERE duration =

(

SELECT MAX(duration)

FROM TABLE1

)

5.

SELECT venue,fdate

FROM TABLE1

WHERE duration =

(

SELECT MIN(duration)

FROM TABLE1

)

more is on the way

Everything you can imagine is real.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Why not SELECT TOP 1 * FROM Table1 ORDER BY Duration [ASC|DESC]

??

Thomas Bartlett SSC Enthusiast Points: 182 More actions · Answer 5

Thanks for such a quick reply. I want to store this process and be able to reuse quickly and easily... so far as it is possible I have already done this successfully in Excel using VBA but it's so easily knocked out of kilter and time consuming, I'd like a more robust solution, e.g a stored procedure in my SQL server DB.

DDL:

CREATE TABLE

dbo.quartiles

(venue varchar, date datetime, los int)

SAMPLE DATA:

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,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

Required result:

Venue,Date,Min,LIR,Med,UIR,Max

Coombehaven,05/08/2007 00:00,1,7.75,16.5,37,290

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

bledu SSChampion Points: 12335 More actions · Answer 6

Ninja,

got that one right. KISS

Everything you can imagine is real.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

August 13, 2007 at 10:09 am

#726517

Nah, I'm more of a Bon Jovi fan .

bledu SSChampion Points: 12335 More actions · Answer 8

proof of concept:

try this for the median and see if its giving you the correct median, replace with your own code where appropriate

-------------

declare

@functions table

(

id

int identity(1,1),

venue vachar

(5),

fdate

datetime,

duration

int

)

INSERT

INTO @functions

(

venue,fdate,duration)

SELECT

venue,fdate,duration

FROM

TABLE1

ORDER

BY duration ASC

--get the median

DECLARE

@medianPtr decimal

declare

@median int

DECLARE

@count int

SELECT

@count = count(*)

FROM

@functions

--check if the count is odd numbered, using modulo

IF

(@COUNT % 2) > 0

BEGIN

--its an odd numbered list

SELECT @medianPtr = (@count + 1)/2

SELECT @median = duration

FROM @functions

WHERE id = @medianPtr

END

ELSE

BEGIN

--get the average of the two middle numbers

SELECT @medianPtr = (@count + 1)/2

SELECT @median = duration

FROM @functions

WHERE id = FLOOR(@medianPtr)

SELECT @median = (duration + @median)/2

FROM @functions

WHERE id = CEILING(@medianPtr)

END

--show the median number

SELECT

@median

Everything you can imagine is real.

Thomas Bartlett SSC Enthusiast Points: 182 More actions · Answer 9

This is great stuff guys. It's gone 5pm here in the UK so I'll get back to you in the morning. Thanks for your suggestions.

Tom

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Ninja's_RGR'us

SSC Guru

Points: 294069

August 13, 2007 at 10:13 am

#726520

HTH.

Good night.

bledu SSChampion Points: 12335 More actions · Answer 11

Thomas,

there is something called taking the problem home.

good night

Everything you can imagine is real.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Ninja's_RGR'us

SSC Guru

Points: 294069

August 13, 2007 at 12:12 pm

#726567

Never heard that one... what does it mean?

bledu SSChampion Points: 12335 More actions · Answer 13

it means working from home.

Everything you can imagine is real.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Ninja's_RGR'us

SSC Guru

Points: 294069

August 13, 2007 at 12:52 pm

#726581

Even better to solve it at work .