Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    7

    Unanswered: Eliminating duplicates from joins

    I have a table and I am grouping by one column. However I have 2 other columns. One is a place holder, the other sums the first cell. so for x

    x y b (sum y over x)
    1 1 1
    1 0 1
    1 0 1
    2 1 3
    2 1 3
    2 1 3
    3 1 2
    3 1 2
    3 0 2

    I group by x. How can I just see 1 record per x?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What results do you want?

  3. #3
    Join Date
    May 2012
    Posts
    7

    Eliminating duplicates from joins

    x y b (sum y over x)
    1 1 1
    1 0 1
    1 0 1
    2 1 3
    2 1 3
    2 1 3
    3 1 2
    3 1 2
    3 0 2

    I'd want

    x y b
    1 1
    2 3
    3 2

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try something like that:

    Code:
    select distinct 
        t.x,
        (select sum(s.y) from MyTable as s where s.x = t.x) as b 
    from MyTable as t
    Hope this helps.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT x
         , SUM(y)
      FROM daTable
    GROUP
        BY x
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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