Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: SUM for DISTINCT rows

    Hi,

    I've been using SQL for a long time, but only very occasionally. So I get what I want with basic SELECT queries and then do the more complicated stuff in code.

    For my current project I try to do something that I guess is trivial in SQL, but I can't get it to work

    I'm using SQLLite, table has two colums
    Amount | AcctNo
    ------------------
    10 | 9113.12
    15 | 9113.12
    30 | 9114.10

    I want a SUM for the DISTINCT AcctNO's. So the result should be
    9113.12 | 25
    9114.10 | 30

    I've tried things like:
    SELECT SUM(amount) FROM tbl WHERE acctno IN (SELECT DISTINCT AcctNo FROM tbl)

    But I'm never getting anything more then 1 line

    All suggestions welcome!

    Bas

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT acctno, SUM(amount) FROM tbl GROUP BY acctno

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

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    That euhmm query is incredibly complex I would have never been able to come up with that

    TX!
    Bas

    P.s What book would you recommend to get past the basic SELECT query level?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by brijniersce
    P.s What book would you recommend to get past the basic SELECT query level?
    this is a trick question, right?

    based on what i think you know about GROUP BY, i would say my book

    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
  •