Post reply

Calculating Median - Help

  • August 1, 2007 at 8:31 am

    #174011

    How do I calculate Median value for a set of records using a simple query.

    Thanks in advance for your help.

    San.

  • August 1, 2007 at 9:06 am

    #723907

    See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22242

    Last post be me is about SQL Server 2005.

     

    N 56°04'39.16"
    E 12°55'05.25"

  • August 1, 2007 at 9:34 am

    #723929

    Thanks for the reply. My data looks something like below.

    DeptTime
    8907
    8310
    8188
    81236
    8417
    852492
    8824
    81821
    8678
    8168
    846292
    8389
    8771
    81252
    8889
    832
    8826
    8243
    852777
    852476
    82081
    865394
    853992
    843845
    8667
    8266
    847614
    84259
    81137
    847838
    10488266
    10995
    10186802
    10327392
    1011829
    1040
    10343827
    10358
    10339
    101697
    10104
    10553
    10187859
    10398
    10400447
    2215261
    22245682
    26181898
    26219
    262371
    26146
    26415
    261145
    263056
    262530
    261520
    265342
    26737
    261395
    261143
    26623
    266191
    26301
    2653
    2685
    265263
    26766
    2692998
    26105
    26626350
    26532
    2679655
    26481
    261649
    26447
    2642
    2673
    26340
    261778
    26345
    2632
    264301
    261554
    26343
    261496
    2612138
    26117
    26426
    26765
    2642
    26500
    26411
    26438
    2635036
    266282

    I need to get Median value for Dept.

    Thanks

    San.

  • August 1, 2007 at 4:06 pm

    #724015

    Median per dept?

    Didn't you see the technique I used with row_number() function...?

     

    N 56°04'39.16"
    E 12°55'05.25"

  • August 1, 2007 at 7:28 pm

    #724038

    Thanks. We are also running these reports in access till we fully migrate to Sql Server. So should work both in Access as well as Sql Server. Any help would be greatly appreciated.

    San.

  • August 1, 2007 at 10:09 pm

    #724061

    If you're partially migrated, do it in SQL Server, now.  If you continue to build new things in the old system, you will simply prolong the migration and maybe even give managers an excuse not to do it at all.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 2, 2007 at 1:11 am

    #724080

    -- Prepare sample data

    DECLARE @Sample TABLE (Dept INT, Time INT)

     

    INSERT     @Sample

    SELECT     8, 907 UNION ALL

    SELECT     8, 310 UNION ALL

    SELECT     8, 188 UNION ALL

    SELECT     8, 1236 UNION ALL

    SELECT     8, 417 UNION ALL

    SELECT     8, 52492 UNION ALL

    SELECT     8, 824 UNION ALL

    SELECT     8, 1821 UNION ALL

    SELECT     8, 678 UNION ALL

    SELECT     8, 168 UNION ALL

    SELECT     8, 46292 UNION ALL

    SELECT     8, 389 UNION ALL

    SELECT     8, 771 UNION ALL

    SELECT     8, 1252 UNION ALL

    SELECT     8, 889 UNION ALL

    SELECT     8, 32 UNION ALL

    SELECT     8, 826 UNION ALL

    SELECT     8, 243 UNION ALL

    SELECT     8, 52777 UNION ALL

    SELECT     8, 52476 UNION ALL

    SELECT     8, 2081 UNION ALL

    SELECT     8, 65394 UNION ALL

    SELECT     8, 53992 UNION ALL

    SELECT     8, 43845 UNION ALL

    SELECT     8, 667 UNION ALL

    SELECT     8, 266 UNION ALL

    SELECT     8, 47614 UNION ALL

    SELECT     8, 4259 UNION ALL

    SELECT     8, 1137 UNION ALL

    SELECT     8, 47838 UNION ALL

    SELECT     10, 488266 UNION ALL

    SELECT     10, 995 UNION ALL

    SELECT     10, 186802 UNION ALL

    SELECT     10, 327392 UNION ALL

    SELECT     10, 11829 UNION ALL

    SELECT     10, 40 UNION ALL

    SELECT     10, 343827 UNION ALL

    SELECT     10, 358 UNION ALL

    SELECT     10, 339 UNION ALL

    SELECT     10, 1697 UNION ALL

    SELECT     10, 104 UNION ALL

    SELECT     10, 553 UNION ALL

    SELECT     10, 187859 UNION ALL

    SELECT     10, 398 UNION ALL

    SELECT     10, 400447 UNION ALL

    SELECT     22, 15261 UNION ALL

    SELECT     22, 245682 UNION ALL

    SELECT     26, 181898 UNION ALL

    SELECT     26, 219 UNION ALL

    SELECT     26, 2371 UNION ALL

    SELECT     26, 146 UNION ALL

    SELECT     26, 415 UNION ALL

    SELECT     26, 1145 UNION ALL

    SELECT     26, 3056 UNION ALL

    SELECT     26, 2530 UNION ALL

    SELECT     26, 1520 UNION ALL

    SELECT     26, 5342 UNION ALL

    SELECT     26, 737 UNION ALL

    SELECT     26, 1395 UNION ALL

    SELECT     26, 1143 UNION ALL

    SELECT     26, 623 UNION ALL

    SELECT     26, 6191 UNION ALL

    SELECT     26, 301 UNION ALL

    SELECT     26, 53 UNION ALL

    SELECT     26, 85 UNION ALL

    SELECT     26, 5263 UNION ALL

    SELECT     26, 766 UNION ALL

    SELECT     26, 92998 UNION ALL

    SELECT     26, 105 UNION ALL

    SELECT     26, 626350 UNION ALL

    SELECT     26, 532 UNION ALL

    SELECT     26, 79655 UNION ALL

    SELECT     26, 481 UNION ALL

    SELECT     26, 1649 UNION ALL

    SELECT     26, 447 UNION ALL

    SELECT     26, 42 UNION ALL

    SELECT     26, 73 UNION ALL

    SELECT     26, 340 UNION ALL

    SELECT     26, 1778 UNION ALL

    SELECT     26, 345 UNION ALL

    SELECT     26, 32 UNION ALL

    SELECT     26, 4301 UNION ALL

    SELECT     26, 1554 UNION ALL

    SELECT     26, 343 UNION ALL

    SELECT     26, 1496 UNION ALL

    SELECT     26, 12138 UNION ALL

    SELECT     26, 117 UNION ALL

    SELECT     26, 426 UNION ALL

    SELECT     26, 765 UNION ALL

    SELECT     26, 42 UNION ALL

    SELECT     26, 500 UNION ALL

    SELECT     26, 411 UNION ALL

    SELECT     26, 438 UNION ALL

    SELECT     26, 35036 UNION ALL

    SELECT     26, 6282 UNION ALL

     

    -- Show the expected output

    SELECT     d.Dept,

               AVG(d.Time) AS Median

    FROM       (

                          SELECT     Dept,

                                     ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Time) AS sort_asc,

                                     ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Time DESC) AS sort_desc,

                                     Time

                          FROM       @Sample

               ) AS

    WHERE      d.sort_asc - d.sort_desc BETWEEN -1 AND 1

    GROUP BY   d.Dept

    ORDER BY   d.Dept

    N 56°04'39.16"
    E 12°55'05.25"

  • August 2, 2007 at 8:16 am

    #724186

    Thanks peter. How to accomplish the same in Access. Really appreciate your help.

    San.

  • August 2, 2007 at 6:20 pm

    #724359

    Sam, I think step 1 would be to post you question in the Access forum.  Lots of us just don't go anywhere near Access and, as you know, the solutions can be quite different. 

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 3, 2007 at 10:58 am

    #724591

    IF you insist on developing everything in both SQL Server and Access, aren't you really delaying the switching over to SQL Server? Make your life simpler - solve problems in SQL Server and explain that the sooner everything is converted over, the sooner the Access data can be 'modernized'.

  • August 3, 2007 at 12:09 pm

    #724602

    It takes little time to migrate to Sql Server and in the mean time I need this in Access.

    Thanks

    San.

  • August 6, 2007 at 9:47 pm

    #725002

    Any help would be greatly appreciated. Thanks in advance.

    San.

  • August 7, 2007 at 5:01 am

    #725077

    Sam... I know little about MS Access... but couldn't you take one of the solutions Peter pointed and do a little conversion yourself?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 7, 2007 at 8:04 am

    #725149

    Thanks for the reply. I tried but there is no SIGN function in Access and its giving empty resultset. Please advise.

    San.

Viewing 14 posts - 1 through 13 (of 13 total)

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