Loading ...
Sorry, an error occurred while loading the content.

Running Sum queries

Expand Messages
  • bitschon
    Using dated Informix SQL... No inline views, no running sum functionality built in How can I do a running sum query when data is stored this way in a table:
    Message 1 of 5 , May 7, 2009
    • 0 Attachment
      Using dated Informix SQL...

      No inline views, no running sum functionality built in

      How can I do a running sum query when data is stored this way in a table:

      store date week month expected sales

      The users want a query that shows:

      store date week WTD_expected_sales MTD_expected_sales

      Thanks,

      JPS
    • John Warner
      When you say no running SUM() functionality do you mean it is not SQL92 compliant? Does it support JOINs? John Warner
      Message 2 of 5 , May 7, 2009
      • 0 Attachment
        When you say no running SUM() functionality do you mean it is not SQL92
        compliant? Does it support JOINs?

        John Warner

        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of bitschon
        > Sent: Thursday, May 07, 2009 12:59 PM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: [SQLQueriesNoCode] Running Sum queries
        >
        > Using dated Informix SQL...
        >
        > No inline views, no running sum functionality built in
        >
        > How can I do a running sum query when data is stored this way in a
        table:
        >
        > store date week month expected sales
        >
        > The users want a query that shows:
        >
        > store date week WTD_expected_sales MTD_expected_sales
        >
        > Thanks,
        >
        > JPS
        >
        >
        >
        > ------------------------------------
        >
        > Yahoo! Groups Links
        >
        >
        >
      • john sayre
        There are no running sum capabilities in SQL92, near as I know, either...in that row 2 wtd sales = row1.sales  +  row2.sales ... From: John Warner
        Message 3 of 5 , May 7, 2009
        • 0 Attachment
          There are no running sum capabilities in SQL92, near as I know, either...in that row 2 wtd sales = row1.sales  +  row2.sales

          --- On Thu, 5/7/09, John Warner <john@...> wrote:
          From: John Warner <john@...>
          Subject: RE: [SQLQueriesNoCode] Running Sum queries
          To: SQLQueriesNoCode@yahoogroups.com
          Date: Thursday, May 7, 2009, 12:13 PM

















          When you say no running SUM() functionality do you mean it is not SQL92

          compliant? Does it support JOINs?



          John Warner



          > -----Original Message-----

          > From: SQLQueriesNoCode@ yahoogroups. com

          > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of bitschon

          > Sent: Thursday, May 07, 2009 12:59 PM

          > To: SQLQueriesNoCode@ yahoogroups. com

          > Subject: [SQLQueriesNoCode] Running Sum queries

          >

          > Using dated Informix SQL...

          >

          > No inline views, no running sum functionality built in

          >

          > How can I do a running sum query when data is stored this way in a

          table:

          >

          > store date week month expected sales

          >

          > The users want a query that shows:

          >

          > store date week WTD_expected_ sales MTD_expected_ sales

          >

          > Thanks,

          >

          > JPS

          >

          >

          >

          > ------------ --------- --------- ------

          >

          > Yahoo! Groups Links

          >

          >

          >




























          [Non-text portions of this message have been removed]
        • John Warner
          The SUM aggregate I thought was well into SQL by 92. Thanks for setting me straight. John Warner ... either...in that ... SQL92
          Message 4 of 5 , May 7, 2009
          • 0 Attachment
            The SUM aggregate I thought was well into SQL by 92. Thanks for setting me
            straight.

            John Warner


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of john sayre
            > Sent: Thursday, May 07, 2009 3:29 PM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: RE: [SQLQueriesNoCode] Running Sum queries
            >
            > There are no running sum capabilities in SQL92, near as I know,
            either...in that
            > row 2 wtd sales = row1.sales  +  row2.sales
            >
            > --- On Thu, 5/7/09, John Warner <john@...> wrote:
            > From: John Warner <john@...>
            > Subject: RE: [SQLQueriesNoCode] Running Sum queries
            > To: SQLQueriesNoCode@yahoogroups.com
            > Date: Thursday, May 7, 2009, 12:13 PM
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > When you say no running SUM() functionality do you mean it is not
            SQL92
            >
            > compliant? Does it support JOINs?
            >
            >
            >
            > John Warner
            >
            >
            >
            > > -----Original Message-----
            >
            > > From: SQLQueriesNoCode@ yahoogroups. com
            >
            > > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of bitschon
            >
            > > Sent: Thursday, May 07, 2009 12:59 PM
            >
            > > To: SQLQueriesNoCode@ yahoogroups. com
            >
            > > Subject: [SQLQueriesNoCode] Running Sum queries
            >
            > >
            >
            > > Using dated Informix SQL...
            >
            > >
            >
            > > No inline views, no running sum functionality built in
            >
            > >
            >
            > > How can I do a running sum query when data is stored this way in a
            >
            > table:
            >
            > >
            >
            > > store date week month expected sales
            >
            > >
            >
            > > The users want a query that shows:
            >
            > >
            >
            > > store date week WTD_expected_ sales MTD_expected_ sales
            >
            > >
            >
            > > Thanks,
            >
            > >
            >
            > > JPS
            >
            > >
            >
            > >
            >
            > >
            >
            > > ------------ --------- --------- ------
            >
            > >
            >
            > > Yahoo! Groups Links
            >
            > >
            >
            > >
            >
            > >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > [Non-text portions of this message have been removed]
            >
            >
            >
            > ------------------------------------
            >
            > Yahoo! Groups Links
            >
            >
            >
          • peternilsson42
            ... SUM is certainly in SQL92, however, cumulative sums are not. Oracle has analytical functions , one form of which is a cumulative sum... select n as N ,
            Message 5 of 5 , May 7, 2009
            • 0 Attachment
              "John Warner" <john@...> wrote:
              >
              > The SUM aggregate I thought was well into SQL by 92.

              SUM is certainly in SQL92, however, cumulative sums are not.

              Oracle has 'analytical functions', one form of which is a
              cumulative sum...

              select n as "N",
              n*n as "N^2",
              sum(n*n) over (order by n) as "Sum(N^2)"
              from (select level as n from dual
              connect by level <= 10)
              order by n;

              N N^2 Sum(N^2)
              -- --- --------
              1 1 1
              2 4 5
              3 9 14
              4 16 30
              5 25 55
              6 36 91
              7 49 140
              8 64 204
              9 81 285
              10 100 385

              Without a genuine cumulative sum, you'd typically need two
              table scans...

              with
              table_of_n as
              (select level as n from dual connect by level <= 10)
              --
              select t1.n as "N",
              t1.n * t1.n as "N^2",
              (select sum(t2.n*t2.n)
              from table_of_n t2
              where t2.n <= t1.n) as "Sum(N^2)"
              from table_of_n t1
              where t1.n <= 10
              order by t1.n;

              ...or...

              with
              table_of_n as
              (select level as n from dual connect by level <= 10)
              --
              select t1.n as "N",
              t1.n * t1.n as "N^2",
              sum(t2.n * t2.n) as "Sum(N^2)"
              from table_of_n t1
              join table_of_n t2 on t2.n <= t1.n
              group by t1.n
              order by t1.n;


              I have no idea about Informix though (the OP's system.)

              --
              Peter
            Your message has been successfully submitted and would be delivered to recipients shortly.