Professional Web Applications Themes

Using max() with GROUP BY - Microsoft SQL / MS SQL Server

select subm_no, init_prem, sum(tot_prem) from premium where subm_no = 39792 group by subm_no I know the above statement will tell me that init_prem "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". Normally I will deal with it my putting any such columns (those that I want in the SELECT list but are not in the GROUP BY) inside a max() function as in max(init_prem). I then consider the problem solved. But is this always a legitimate and adequate solution? Are there other ways to handle this situation?...

  1. #1

    Default Using max() with GROUP BY

    select subm_no, init_prem, sum(tot_prem)
    from premium
    where subm_no = 39792
    group by subm_no

    I know the above statement will tell me that init_prem "is invalid in the
    select list because it is not contained in either an aggregate function
    or the GROUP BY clause". Normally I will deal with it my putting any
    such columns (those that I want in the SELECT list but are not in the
    GROUP BY) inside a max() function as in max(init_prem). I then consider
    the problem solved. But is this always a legitimate and adequate
    solution? Are there other ways to handle this situation?
    Rick Guest

  2. #2

    Default Re: Using max() with GROUP BY

    In the example you provided, using MAX, MIN, or whichever aggregate function
    works for you is the way to go.
    A similar request that is not that trivial is when you want to return a set
    of columns from the same row, but only one row for each distinct combination
    of a subset of those columns, e.g.,

    -- invalid
    SELECT col1, col2, col3, col4, col5, ...
    FROM T1
    GROUP BY col1, col2

    The following is a valid query:

    SELECT col1, col2, MAX(col3), MAX(col4), MAX(col5), ...
    FROM T1
    GROUP BY col1, col2

    But MAX(col3), MAX(col4), MAX(col5), ..., might return values originating
    from different rows.
    Of course, there's a missing factor in the request: out of the duplicates
    with the same col1, col2 values, which should be returned?
    If you provide the missing factor, you can also produce a query accordingly,
    e.g., the one with the max value in keycol, which is unique (at least within
    col1, col2):

    SELECT col1, col2, col3, col4, col5, ...
    FROM T1 AS A
    WHERE keycol =
    (SELECT MAX(keycol)
    FROM T1 AS B
    WHERE B.col1 = A.col1
    AND B.col2 = A.col2)

    --
    BG, SQL Server MVP
    Solid Quality Learning
    www.solidqualitylearning.com


    "Rick C." <com> wrote in message
    news:microsoft.com... 


    Itzik Guest

  3. #3

    Default Re: Using max() with GROUP BY

    >> I know the above statement will tell me that init_prem "is invalid in
    the select list because it is not contained in either an aggregate
    function or the GROUP BY clause". Normally I will deal with it my
    putting any
    such columns (those that I want in the SELECT list but are not in the
    GROUP BY) inside a max() function as in max(init_prem). I then consider
    the problem solved. But is this always a legitimate and adequate
    solution? Are there other ways to handle this situation? <<

    Are you an old Sybase programmer? That product had this flaw for a
    looooong time and people actually wrote code to it, even tho it makes no
    sense. My favorite was to have a table Villes(city, state) and run this
    query:

    SELECT state, city, COUNT(*)
    FROM Villes
    GROUP BY state;

    to show that there were two Nashvilles in TN.

    The answer is "depends what you are trying to do!!"





    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. Moderated Post

    Default Re: Using max() with GROUP BY

    Removed by Administrator
    Rick Guest
    Moderated Post

  5. #5

    Default Re: Using max() with GROUP BY

    Rick,

    You may be certain that the value for init_prem is
    the same for each subm_no group, but SQL Server
    doesn't know that, which is why it does not allow you to
    ask the question.

    If the value of init_prem is determined by the value of
    subm_no alone, your table is not normalized, because
    unless you have left out some constraints, your design
    allows for more than one init_prem value to be associated
    with a single subm_no value.

    Grouping on init_prem is the simplest solution, but if
    you want to simplify this and avoid inconsistent data,
    you could normalize and have a separate table to hold
    the relation between subm_no and init_prem, which lets
    you do something like this:

    select P.subm_no, N.init_prem, P.tot_prem_sum
    from (
    select premium.subm_no, sum(premium.tot_prem) as tot_prem_sum
    from premium
    where premium.subm_no = 39792
    group by subm_no
    ) P, NewTable N
    where P.subm_no = N.subm_no

    You can't do this as easily now, since you have no table that
    contains exactly one instance of the fact "the value of init_prem is
    #### for the subm_no value 39792.

    Steve Kass
    Drew University


    Rick C. wrote:
     
    >
    >
    >In article <phx.gbl>,
    >solidqualitylearning.com says...
    >
    > [/ref]

    Steve Guest

  6. #6

    Default Re: Using max() with GROUP BY

    >> But I'm still not sure after reading this how I can write my query
    differently. <<

    select subm_no, init_prem, sum(tot_prem)
    from premium
    where subm_no = 39792
    group by subm_no, init_prem;


    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  7. #7

    Default Re: Using max() with GROUP BY

    > init_prem; the value for init_prem in all the rows IS THE SAME.

    then it's easy:

    SELECT subm_no, init_prem, SUM(tot_prem)
    FROM premium
    WHERE subm_no = 39792
    GROUP BY subm_no, init_prem

    But as Steve has said, in that case your table is not in 3NF.

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



    David Guest

Similar Threads

  1. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  2. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. Hi to the group
    By SES in forum Adobe Photoshop Elements
    Replies: 38
    Last Post: June 28th, 11:30 AM

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