Results 1 to 10 of 10

Thread: SUM function

  1. #1
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31

    Unanswered: SUM function

    I have this query:
    Code:
    SELECT SUM(st_Count) FROM Queue WHERE st_ID = 1483
    It works, but how can I change it, to see SUM for many ID's, which I have in list? I try this:
    Code:
    SELECT SUM(st_Count) FROM Queue WHERE st_ID = List.tw_ID
    But there is error:
    Code:
    The multi-part identifier "List.tw_ID" could not be bound.
    How to display SUM for meny ID's?

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    -- set up the test - would have been nice if you had included it
    Code:
    create table #temp (idcol char(1) , rcount int);
    insert into #temp
    select '1', 1
    UNION
    select '1', 2
    UNION
    select '1', 3
    UNION
    select '2', 2
    UNION
    select '2', 3
    UNION
    select '2', 4
    UNION
    select '3', 3
    UNION
    select '3', 4
    UNION
    select '3', 5
    UNION
    select '4', 5
    UNION
    select '4', 6
    UNION
    select '5', 7
    UNION
    select '6', 8;
    select idcol, sum(rcount)
    from #temp
    group by idcol
    order by idcol

    -- results
    Code:
    idcol             
    ----- ----------- 
    1     6
    2     9
    3     12
    4     11
    5     7
    6     8
    (6 row(s) affected)

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    There is error, not result
    And result for first query is:
    "(No column name)"
    "267,00"
    "(1 row(s) affected)"

    My question is: How to write query like first, for each ID (column, called "ID") in List -> (List.tw_ID)
    "1482" is one of rows: there is few more, and I want to get one result (SUM of numbers in rows with the same ID) for each ID in List.
    Last edited by Ivenesco; 02-07-08 at 16:48.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Tom, you'd be better you stabbing yourself in the temple with an icepick
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT st_ID
         , SUM(st_Count) as ID_Count
      FROM Queue 
     WHERE st_ID IN ( some list )
    GROUP
        BY st_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    Quote Originally Posted by r937
    Code:
    SELECT st_ID
         , SUM(st_Count) as ID_Count
      FROM Queue 
     WHERE st_ID IN ( some list )
    GROUP
        BY st_ID
    Code:
    The multi-part identifier "List.tw_Id" could not be bound.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There is no table called List or aliased List in your query? Everything you display or compare to MUST be defined in the FROM clause.

  8. #8
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    Query and List are tables.
    Code:
    st_TowId IN (Lista.tw_Id)
    "IN" can't be instead of "FROM"?

    Ok, thanks! I add "List" to FROM, and it works.
    Code:
    SELECT SUM(Queue.st_Count)
    FROM Queue, List
    WHERE Queue.st_Id = List.Id
    But another problem.... It create only one row... How to create one row for each SUM?
    Last edited by Ivenesco; 02-07-08 at 17:26.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ivenesco
    How to create one row for each SUM?
    see post #5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    I overlooked it, thanks! Now works perfectly

Posting Permissions

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