Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Arrow Unanswered: [newbie] SQL-query with SUM() doesn't work. :(

    Hi NG,

    I have two tables:

    TABLE1 (id1 INTEGER NOT NULL, id2 INTEGER NOT NULL, value INTEGER, PRIMARY KEY (id1, id2))

    TABLE2 (id1 INTEGER NOT NULL, text CHAR(4), PRIMARY KEY (id1))

    And this is the query, that I'm trying to run on this tables:

    select TABLE1.id1, TABLE2.text, sum(TABLE.value)
    from table1, table2
    where TABLE1.id1=TABLE2.id1
    group by TABLE1.id1
    order by TABLE1.id1 asc

    Well - it doesn't work at all!
    That is the reason?!

    Appreciate everyone's help!!!

    S.B.

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    group by

    well when doing aggregates you must have everything in the select statement that is not being summed in the group by clause.

    Example:

    select t1.id1, t2.text, sum(t1.value)
    from table1 t1, table2 t2
    where t1.id1 = t2.id1
    group by t1.id1, t2.text
    order by t1.id1 asc

  3. #3
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Exclamation no group column

    Hello,

    select TABLE1.id1, TABLE2.text, sum(TABLE.value)
    from table1, table2
    where TABLE1.id1=TABLE2.id1
    group by TABLE1.id1
    order by TABLE1.id1 asc

    DROP the TABLE2.text field cause this is not a group field or
    use

    select TABLE1.id1, TABLE2.text, sum(TABLE.value)
    from table1, table2
    where TABLE1.id1=TABLE2.id1
    group by TABLE1.id1, TABLE2.text
    order by TABLE1.id1 asc

    Hope that helps ?

    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com

  4. #4
    Join Date
    Oct 2003
    Posts
    18

    Re: no group column

    Thanks, mkkmg and alligatorsql.com !!!

    "group by TABLE1.id1, TABLE2.text" works perfectly!

    S.B.


    Originally posted by alligatorsql.com
    Hello,

    select TABLE1.id1, TABLE2.text, sum(TABLE.value)
    from table1, table2
    where TABLE1.id1=TABLE2.id1
    group by TABLE1.id1
    order by TABLE1.id1 asc

    DROP the TABLE2.text field cause this is not a group field or
    use

    select TABLE1.id1, TABLE2.text, sum(TABLE.value)
    from table1, table2
    where TABLE1.id1=TABLE2.id1
    group by TABLE1.id1, TABLE2.text
    order by TABLE1.id1 asc

    Hope that helps ?

    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com

Posting Permissions

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