Re: Running Sum field in select query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Adam Machanic (amachanic_at_air-worldwide.nospamallowed.com)
Date: 03/24/04 

Date: Tue, 23 Mar 2004 19:14:32 -0500

You forgot a couple of _1 :

Select tblsample.Request_ID, tblsample.Div_Count AS
Division_Count,
(Select sum(tblsample_1.div_Count) from tblsample as
tblsample_1
where tblsample_1.Request_ID <= tblsample.Request_ID and
tblsample_1.Current_Process_Date > '10/1/2003' and -- this line
tblsample_1.Request_Type = 'GFU')as RunningTotal -- and this
line
>From tblsample
Where tblsample.Current_Process_Date > '10/1/2003' and
tblsample.Request_Type = 'GFU'
ORDER BY tblsample.request_id

"Ashley" <anonymous@discussions.microsoft.com> wrote in message
news:1236f01c41132$d18d1e60$a301280a@phx.gbl...
> Hopefully this isn't too hard to read
> Sample Input Data:
>
> Request_ID Request_type div_count Current_Process_Date
>
> 1 ELIG 3 10/1/03
> 2 GFU 10 1/1/03
> 3 GFU 12 11/1/03
> 4 ELIG 5 12/1/03
> 5 ELIG 8 3/1/03
> 6 GFU 15 10/20/03
> 7 GFU 2 7/1/03
> 8 ELIG 1 8/1/03
> 9 GFU 3 12/1/03
> 10 GFU 2 2/1/03
> 11 ELIG 5 12/1/03
> 12 GFU 1 11/15/03
> 13 GFU 11 1/11/03
> 14 GFU 8 11/11/03
>
> Query:
> Select tblsample.Request_ID, tblsample.Div_Count AS
> Division_Count,
> (Select sum(tblsample_1.div_Count) from tblsample as
> tblsample_1
> where tblsample_1.Request_ID <= tblsample.Request_ID and
> tblsample.Current_Process_Date > '10/1/2003' and
> tblsample.Request_Type = 'GFU')as RunningTotal
> From tblsample
> Where tblsample.Current_Process_Date > '10/1/2003' and
> tblsample.Request_Type = 'GFU'
> ORDER BY tblsample.request_id
>
> Output:
> Request_ID Div_Count Runningtotal
> 3 12 25
> 6 15 53
> 9 3 59
> 12 1 67
> 14 8 86
>
>
> The running total should be 12, 27, 30, 31, and 8.
> Instead it is totalling the entire div_count column.
>
> Thanks so much for your help!
> Ashley
>
>
> >-----Original Message-----
> >I suggest you post DDL, sample input data, and sample
> output data, and we'll
> >come up with something.
> >
> >
> >"Ashley" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:1232201c4112a$c5494af0$a301280a@phx.gbl...
> >> I tried this but the problem is that my query is a
> little
> >> more complicated. I am pulling certain records based
> on a
> >> variety of selection criteria.
> >> This messes up the running total - it seems to be
> >> including records in the sum that should not be
> included.
> >> Any suggestions?
> >>
> >> - Ashley
> >>
> >>
> >> >-----Original Message-----
> >> >This is the easiset way (assuming that Descr can be
> used
> >> to identify
> >> >previous rows... if not, substitute in whatever your
> >> primary key is):
> >> >
> >> >
> >> >SELECT T1.Descr, T1.DIVCount, RunningTotal = (SELECT
> SUM
> >> (DIVCount) FROM
> >> >MyTable T2 WHERE T2.Descr <= T1.Descr)
> >> >FROM MyTable T1
> >> >
> >> >
> >> >"Ashley" <anonymous@discussions.microsoft.com> wrote in
> >> message
> >> >news:d8e901c41111$998d5870$a001280a@phx.gbl...
> >> >> Hi,
> >> >> I am trying to create a select query that selects
> >> various
> >> >> fields from various tables and then I want to create
> a
> >> new
> >> >> column through the query that is a running sum of
> one of
> >> >> the fields I am selecting.
> >> >> Simplified Example:
> >> >>
> >> >> Table looks like:
> >> >>
> >> >> Descr DIVCount
> >> >> ------------------------
> >> >> RID1 1
> >> >> RID2 10
> >> >> RID3 5
> >> >>
> >> >> And I want to create a query that takes this data and
> >> adds
> >> >> another column that is the running total.
> >> >> Results will look like:
> >> >>
> >> >> Descr DIVCount RunningTotal
> >> >> ------------------------------------------------
> >> >> RID1 1 1
> >> >> RID2 10 11
> >> >> RID3 5 16
> >> >>
> >> >> How can I create a query to do this??
> >> >> Your help is much appreciated!
> >> >>
> >> >> Ashley
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Relevant Pages

  • Re: Running Sum field in select query
    ... from tblsample as ... >I suggest you post DDL, sample input data, and sample ... output data, and we'll ... >> I tried this but the problem is that my query is a ...
    (microsoft.public.sqlserver.programming)
  • Re: Running Sum field in select query
    ... Dumb mistake. ... >> From tblsample ... >> output data, and we'll ... >> SUM ...
    (microsoft.public.sqlserver.programming)