Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    32

    Unanswered: join two table with group by

    Hi

    I want to join 2 tables and produce the result in the result table.


    table 1:
    id, name
    1, bob
    2, tom

    table2:
    id, order
    1,2
    1,3

    result
    id,name,order
    1,bob,5

    I have the following sql, but I am getting the order number assigning to every id.
    select a,id, a,name,sum(b,order) from table1 a, table2 b where a.id=b.id group by (b.order)

    What have I done wrong??

    Much Appreciated

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT a.id
         , a.name
         , SUM(b.order) AS total
      FROM table1 AS a
    INNER
      JOIN table2 AS b 
        ON b.id = a.id 
    GROUP 
        BY a.id
         , a.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    32
    Thanks r930
    I tried the two tables and it works.
    However, I need to join table3 for more information

    table3
    id,gender
    1,M

    I am getting a syntax error with the following sql

    select a.id, a.name, sum(b.order), c.gender from table3 as c, table1 as a inner join table2 as b on b.id = a.id where a.id=c.id and c.gender='M' group by a.id, a.name

    Do you know the problem with my sql?

    Much Appreciated

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tiger66
    Do you know the problem with my sql?
    please allow me to give you a suggestion -- don't post half a problem, wait until someone hands you a working solution (for free), and then change the problem

    your SQL is wrong in a couple of places -- you're mixing comma-separated inner joins with JOIN syntax (don't do that, just stick to JOIN syntax), and you have non-aggregate columns in the SELECT clause that aren't in the GROUP BY clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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