Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82

    Unanswered: Aggregaton function withou group by

    I need explain function of agregation query...

    I have table

    create table t_test( id int, shortcut varchar(5), coment varchar(10) )

    insert t_test values (1, "X", "this is X" )
    insert t_test values (1, "X", "this is X" )
    insert t_test values (2, "Y", "this is Y" )
    insert t_test values (2, "Y", "this is Y" )
    insert t_test values (3, "Z", "this is Z" )

    Now I run query

    select * from t_test where id = 2

    id shortcut comment
    ----------- ------- -----
    2 Y this is Y
    2 Y this is Y



    Result is clear..

    Now I run this:

    select min(id) , * from t_test where id = 2

    id shortcut comment
    ----------- ----------- ------- -----
    2 1 X this is X
    2 1 X this is X
    2 2 Y this is Y
    2 2 Y this is Y
    2 3 Z this is Z

    I have problem to interpret this... minimum is 2 , so command where id = 2 limited query selection for min(id)... But at the same time all columns from table are showed, so where id = 2 don´t limit selection... How it is?

    David
    Last edited by Musil David; 02-14-07 at 09:07.

  2. #2
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    It is strange...

    query : select min(id) , id, zkratka, popis from t_testovaci where id = 2

    is not possible run on Oracle 10g or MS SQL 2000 ....

    I get...
    Column 't_test.id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
    But why Sybase 12 dont give such message???

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    This is just an easy way to join your aggregate values with the rest of your table.
    The SQL standards for group by are more restrictive than Sybase's standard. The SQL standard requires that:
    · The columns in a select list must be in the group by expression or they must be arguments of aggregate functions.
    · A group by expression can only contain column names in the select list, but not those used only as arguments for vector aggregates.

    Your query translate to
    Code:
    select min(b.id),a.id,a.shortcut,a.coment
    from t_test a, t_test b
    where b.id=2
    group by a.id,a.shortcut,a.coment
    If you want to receive a warning message stating that Transact-SQL extensions are used:
    set fipsflagger on

    Also consider this example
    Code:
    create table t1 (id int, pd char(2), amt decimal(12,3))
    insert into t1 select
    1,'aa', 10.00 union all select 
    2,'aa', 20.00 union all select 
    3,'aa', 30.00 union all select 
    4,'bb',100.00 union all select 
    5,'bb',110.00 union all select 
    6,'cc',  1.00
    
    select id,pd,amt,sum(amt) from t1 group by pd 
    id          pd   amt                                                     
    ----------- --   -------------- ---------------------------------------- 
              1 aa           10.000                                   60.000 
              2 aa           20.000                                   60.000 
              3 aa           30.000                                   60.000 
              4 bb          100.000                                  210.000 
              5 bb          110.000                                  210.000 
              6 cc            1.000                                    1.000 
    
    --which is the same as 
    
    select a.id,a.pd,a.amt,sum(b.amt) 
    from t1 a, t1 b
    where a.pd=b.pd
    group by a.id,a.pd,a.amt
    
    --and if you add a where clause you might not get the expected results e.g.
    select id,pd,amt,sum(amt) from t1 
    where id<=4 group by pd 
    -- is the same as 
    select a.id,a.pd,a.amt,sum(b.amt) 
    from t1 a, t1 b
    where a.pd=b.pd
      and b.id<=4
    group by a.id,a.pd,a.amt
    
    id          pd   amt                                                     
    ----------- --   -------------- ---------------------------------------- 
              1 aa           10.000                                   60.000 
              2 aa           20.000                                   60.000 
              3 aa           30.000                                   60.000 
              4 bb          100.000                                  100.000 
              5 bb          110.000                                  100.000
    This behavior is documented in
    Transact-SQL User’s Guide
    Chapter 3: Using Aggregates, Grouping, and Sorting
    Aggregates without group by


    Also note that outer join queries with a where clause can produce different output between T-SQL syntax and ANSI syntax, but thats another story.
    Last edited by pdreyer; 02-16-07 at 08:34. Reason: Add link to documentation

Posting Permissions

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