sql 2008 get max, min, avg, Stdev , median

Question

I have been working on the ssrs reporting, i task is to get the Fish Max, Min, Avg,Stdev,Medain of length and weight from Catches by group by speciesId , I was able to get Max, Min,Avg of length and weight of each speciesId but the tricky part was get median for length and weight, I have been google for the solution and found different solution but i am not sure which one was right for my case. Here is my temp table

i used SQL 2008

CREATE TABLE #Catchestemp
(
id int, -- Primary key
speciesID int, --foreign key
catchWeight numeric(6,2),
catchLength numeric(6,3)

)

INSERT INTO #Catchestemp (id, speciesID,catchWeight,catchLength)
VALUES 
(1,1,500.00,16.000),
(2,1,160.00,25.000),
(3,2,36.00,13.000),
(4,2,15.00,20.000),
(5,3,25.00,25.000),
(6,3,24.00,128.000),
(7,4,26.00,163.000),
(8,5,24.00,172.000),
(9,5,27.00,172.000),
(10,6,27.00,172.000),
(11,6,25.00,158.000),
(12,6,26.00,134.000),
(13,6,28.00,154.000),
(14,1,240.00,133.000),
(15,1,100.00,114.000),
(16,1,90.00,216.000),
(17,1,50.00,168.000),
(18,7,24.00,115.000),
(19,7,25.00,104.000),
(20,7,27.00,136.000),
(21,7,19.00,74.000),
(22,3,19.00,64.000),
(23,1,50.00,147.000),
(24,1,69.00,146.000),
(25,1,80.00,106.000)

SELECT * from  #Catchestemp
DROP table #Catchestemp

Please help me get this out, I will be very appreciated all the answer Thank you in advance.

I think that that question was already asked hire: http://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server — Dusan, Jun 13 '14 at 18:55

score 0 · Accepted Answer · edited May 23 '17 at 12:13

This query should do it. Uses the solution from Function to Calculate Median in Sql Server

Just replace your "SELECT * from #Catchestemp" query with this.

select a.*,b.MedianWeight,c.MedianLength from (
    SELECT speciesID, min(catchLength) MinLength, max(catchLength) MaxLength, avg(catchLength) AvegrageLength, isnull(STDEV(catchLength),0) StDevLength
    , min(catchWeight) MinWeight, max(catchWeight) MaxWeight, avg(catchWeight) AvegrageWeight, isnull(STDEV(catchWeight),0) StDevWeight
    from  #Catchestemp c
    group by speciesID
) a
join (
    select speciesID, avg(catchWeight) MedianWeight from (
        select speciesID,
            catchWeight,
            ROW_NUMBER() OVER (PARTITION BY speciesID ORDER BY catchWeight ASC, id ASC) AS RowAsc,
            ROW_NUMBER() OVER (PARTITION BY speciesID ORDER BY catchWeight DESC, id DESC) AS RowDesc
        from #Catchestemp
    ) x
    WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
    group by speciesID
) b on a.speciesID = b.speciesID
join (
    select speciesID, avg(catchLength) MedianLength from (
        select speciesID,
            catchLength,
            ROW_NUMBER() OVER (PARTITION BY speciesID ORDER BY catchLength ASC, id ASC) AS RowAsc,
            ROW_NUMBER() OVER (PARTITION BY speciesID ORDER BY catchLength DESC, id DESC) AS RowDesc
        from #Catchestemp
    ) x
    WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
    group by speciesID
) c on a.speciesID = c.speciesID

Thank you jokedst to take time to hlep me solved , I am very appreciated your help, codemonkey Thank you to point me to the link that very helpful. — MC_A, Jun 16 '14 at 14:57

sql 2008 get max, min, avg, Stdev , median

1 Answers1