Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    13

    Unanswered: How to write a Running Total Sum from two tables in DB2?

    Hi,

    I have an issue writing Running Totals from two tables in SQL, I am getting an error from the sql I have written. Could somebody help in correcting this SQL to get the running total query to work? Thanks in advance and below is the query.

    select t1.item, t2.price, t3.col1, t4.col1,
    (select sum(t1.item * t2.price) from t1
    where t1.itemID <= t2.itemID) as summary,
    from .....
    goup by
    t1.item, t2.price, t3.col1, t4.col1

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use OLAP specification SUM(t1.item * t2.price) OVER(...).

    If you gave sample data and expected result, I may be able to write sample SQL.
    My question is ...
    Although you wrote "from two tables",
    your query include "t1.item, t2.price, t3.col1, t4.col1, ...".

  3. #3
    Join Date
    Jun 2011
    Posts
    13

    How to write a Running Total from two tables

    The calculation inside the sum() are the two tables I am talking about, but the main select statement has several tables in it and the query itself is rather very large, there are several outer and inner joins in it, so to simplify it lets just use two tables in the select statemnt t1 and t2 and then do the su m(t1.item*t2.price) from t1 where t1.Id = t2.ID or however it should work.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I am getting an error from the sql I have written.
    What error did you got?

    One poosibility might be that you didn't distinguish t1 from main select and t1 inside sub-select.

    If so, try to specify correlation-name, like...
    select t1.item, t2.price, t3.col1, t4.col1,
    (select sum(t1s.item * t2.price) from t1 AS t1s
    where t1s.itemID <= t2.itemID) as summary,
    from .....
    goup by
    t1.item, t2.price, t3.col1, t4.col1

Posting Permissions

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