Professional Web Applications Themes

Adding aggregate functions - Microsoft SQL / MS SQL Server

You can't add aggregate functions, but you can create stored procedures. Check http://sql.reproms.si, go to Code Library -> White Papers and check the Descriptive Statistics articles. -- Dejan Sarka, SQL Server MVP FAQ from Neil & others at: http://www.sqlserverfaq.com Please reply only to the newsgroups. PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "AJC" <com> wrote in message news:#phx.gbl... ...

  1. #1

    Default Re: Adding aggregate functions

    You can't add aggregate functions, but you can create stored procedures.
    Check http://sql.reproms.si, go to Code Library -> White Papers and check
    the Descriptive Statistics articles.

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org

    "AJC" <com> wrote in message
    news:#phx.gbl... 


    Dejan Guest

  2. #2

    Default Re: Adding aggregate functions

    SQL For Smarties (Celko) has a whole chapter on how to calculate Medians in
    SQL. Other statistics are also covered.
    http://tinyurl.com/hntc

    Here's an example:

    CREATE TABLE SomeValues (keyx CHAR(1) PRIMARY KEY, valuex INTEGER NOT NULL)

    INSERT INTO SomeValues VALUES ('A',1)
    INSERT INTO SomeValues VALUES ('B',2)
    INSERT INTO SomeValues VALUES ('C',3)
    INSERT INTO SomeValues VALUES ('D',4)
    INSERT INTO SomeValues VALUES ('E',5)

    SELECT S1.valuex AS median
    FROM SomeValues AS S1, SomeValues AS S2
    GROUP BY S1.valuex
    HAVING SUM(CASE WHEN S2.valuex <= S1.valuex
    THEN 1 ELSE 0 END) 
    AND SUM(CASE WHEN S2.valuex >= S1.valuex
    THEN 1 ELSE 0 END) 

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "AJC" <com> wrote in message
    news:#phx.gbl... 


    David Guest

  3. #3

    Default Re: Adding aggregate functions

    Thank you for your responses. I'm surprised how complex it is to do what one
    would hope to be a simple thing!

    Cheers

    Andy


    "AJC" <com> wrote in message
    news:%phx.gbl... 


    AJC Guest

Similar Threads

  1. three table outer join with aggregate functions
    By jhilty in forum Coldfusion Database Access
    Replies: 2
    Last Post: July 13th, 09:48 AM
  2. aggregate functions -- does value exist in group
    By jezaustin@gmail.com in forum MySQL
    Replies: 3
    Last Post: August 23rd, 04:46 PM
  3. Replies: 3
    Last Post: July 4th, 01:53 PM
  4. Replies: 2
    Last Post: June 30th, 09:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139