Professional Web Applications Themes

GROUP BY - Microsoft SQL / MS SQL Server

Hi everybody What is the purpose of the Group By clause of SQL i think it's worthless because i have to group on the fields which i select Matthijs ter Woord...

  1. #1

    Default GROUP BY

    Hi everybody

    What is the purpose of the Group By clause of SQL

    i think it's worthless because i have to group on the fields which i select

    Matthijs ter Woord


    Matthijs Guest

  2. #2

    Default Re: GROUP BY

    GROUP BY gives an aggregation (summary) of the base data.

    Example. Total sales by type (from the Pubs database):

    SELECT type, SUM(ytd_sales)
    FROM pubs..Titles
    GROUP BY type

    If you have a specific problem, post DDL for your table(s), post some sample
    data as INSERT statements and give an example of the result you are looking
    for.

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


    David Guest

  3. #3

    Default Re: GROUP BY

    Although you have to group by every column in the select list,
    you can group by columns not in the select list.

    It might have been nice to have a shortcut version of the group by clause
    where the select list is automatically added to the group by clause
    or something like group by SELECT_LIST, additional_column, but Alas, it is
    not the case.
    Nor is there any sign that in future versions of the ANSI SQL standard it
    will become available.

    Every language has it's syntactic quirks and limitations, and its own
    shortcuts as well, so just live with it. And you may grow to love some of
    its advantages...

    HS



    HSalim Guest

  4. #4

    Default Re: GROUP BY

    > Although you have to group by every column in the select list, 

    i want to do the opposite.

    group by 3 columns and get select 4 columns


    Matthijs Guest

  5. #5

    Default Re: GROUP BY

    Matt,
    That is not logical.
    Remember, a group by is an aggregation. So which value is the select
    supposed to return?
    Consider this

    select country, count (*) from customers group by country
    returns
    Argentina 3
    Austria 2
    Belgium 2
    ....

    Now if you add a Customer name to it, which customer name is it supposed to
    return?

    If your answer is all, then I'd say that you are not looking at the question
    correctly.

    You could get what you ask with this:
    Select C.CompanyName,T.Country, T.CustCount from
    customers C join(
    select country, count (*) Custcount from customers
    group by country) as T
    on C.Country = T.Country

    As you can see, the count repeats for each line.

    What is the problem you are having? If you post your SQL as you have it
    now, perhaps someone here might be able to help

    Regards
    Habib


    "Matthijs ter Woord" <nl> wrote in message
    news:phx.gbl... 
    >
    > i want to do the opposite.
    >
    > group by 3 columns and get select 4 columns
    >
    >[/ref]


    HSalim 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. This group
    By mamamia in forum Windows Vista
    Replies: 6
    Last Post: May 5th, 04:32 AM
  4. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 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