I am working on a report and need to figure out a way to come up with
running totals based on a column value. Let me explain...
As shown in the query below, I am displaying "totalmealsize", "minmealsize",
and "maxmealsize" for a particular interval number (which can be anywhere
between 1 and 24). What I would like to do is display a running total for
each interval that summarizes all previous intervals. For example, given
the following data:
INTERVAL TOTAL RUNNING-TOTAL
1 5 5
2 3 8
3 1 9
4 7 16
5 4 20
As you can see, the RUNNING-TOTAL is the sum of all previous interval values
(I would want to do something similar with the min/max values as well). I
have tried various things but have not succeeded. I realize I can do this
in (C#) code as well as probably even create a user-defined function to
handle it. However, I would like to keep it in standard SQL if possible.
Any ideas?
<BEGIN SQL QUERY>
SELECT t.Name, tl.Level, id.sequencenumber, id.intervalnumber,
id.RunAnimalID,
id.totalmealsize AS SMS, id.minmealsize AS SMS_MIN, id.maxmealsize
AS SMS_MAX
FROM intervaldetail id
INNER JOIN TreatmentLevel tl ON id.RunAnimalID = tl.RunAnimalID
INNER JOIN Treatment t on t.TreatmentID = tl.TreatmentID
WHERE id.intervalsetid = 99
ORDER BY t.Name, tl.Level, id.sequencenumber
<END SQL QUERY>
-- Thanks, JeffTry this
Declare @.t table(INTERVAL int, TOTAL integer)
insert into @.T values(1,5)
insert into @.T values(2,3)
insert into @.T values(3,1)
insert into @.T values(4,7)
insert into @.T values(5,4)
select *,(select sum(total) from @.t where interval <=T.Interval) as
'Running Total' from @.t T
Madhivanan|||Here is a repro,
CREATE TABLE #Totals(ID int IDENTITY(1,1), total int)
INSERT INTO #Totals VALUES(100)
INSERT INTO #Totals VALUES(200)
INSERT INTO #Totals VALUES(300)
INSERT INTO #Totals VALUES(400)
SELECT A.ID , A.Total,(SELECT SUM(total)
FROM #Totals B WHERE B.ID <=A.ID) As RunningTotal
FROM #Totals A
DROP TABLE #Totals
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jeff B." <jsb@.community.nospam> wrote in message
news:e6k6GiVHFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I am working on a report and need to figure out a way to come up with
>running totals based on a column value. Let me explain...
> As shown in the query below, I am displaying "totalmealsize",
> "minmealsize", and "maxmealsize" for a particular interval number (which
> can be anywhere between 1 and 24). What I would like to do is display a
> running total for each interval that summarizes all previous intervals.
> For example, given the following data:
> INTERVAL TOTAL RUNNING-TOTAL
> 1 5 5
> 2 3 8
> 3 1 9
> 4 7 16
> 5 4 20
> As you can see, the RUNNING-TOTAL is the sum of all previous interval
> values (I would want to do something similar with the min/max values as
> well). I have tried various things but have not succeeded. I realize I
> can do this in (C#) code as well as probably even create a user-defined
> function to handle it. However, I would like to keep it in standard SQL
> if possible.
> Any ideas?
> <BEGIN SQL QUERY>
> SELECT t.Name, tl.Level, id.sequencenumber, id.intervalnumber,
> id.RunAnimalID,
> id.totalmealsize AS SMS, id.minmealsize AS SMS_MIN, id.maxmealsize
> AS SMS_MAX
> FROM intervaldetail id
> INNER JOIN TreatmentLevel tl ON id.RunAnimalID = tl.RunAnimalID
> INNER JOIN Treatment t on t.TreatmentID = tl.TreatmentID
> WHERE id.intervalsetid = 99
> ORDER BY t.Name, tl.Level, id.sequencenumber
> <END SQL QUERY>
> -- Thanks, Jeff
>|||Thanks everyone for your replies. The subquery did the trick. I had tried
that one before but I must have had the syntax wrong because I wasn't able
to get it to work. Now it is working just fine.
-- Thanks, Jeff
"Jeff B." <jsb@.community.nospam> wrote in message
news:e6k6GiVHFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I am working on a report and need to figure out a way to come up with
>running totals based on a column value. Let me explain...
> As shown in the query below, I am displaying "totalmealsize",
> "minmealsize", and "maxmealsize" for a particular interval number (which
> can be anywhere between 1 and 24). What I would like to do is display a
> running total for each interval that summarizes all previous intervals.
> For example, given the following data:
> INTERVAL TOTAL RUNNING-TOTAL
> 1 5 5
> 2 3 8
> 3 1 9
> 4 7 16
> 5 4 20
> As you can see, the RUNNING-TOTAL is the sum of all previous interval
> values (I would want to do something similar with the min/max values as
> well). I have tried various things but have not succeeded. I realize I
> can do this in (C#) code as well as probably even create a user-defined
> function to handle it. However, I would like to keep it in standard SQL
> if possible.
> Any ideas?
> <BEGIN SQL QUERY>
> SELECT t.Name, tl.Level, id.sequencenumber, id.intervalnumber,
> id.RunAnimalID,
> id.totalmealsize AS SMS, id.minmealsize AS SMS_MIN, id.maxmealsize
> AS SMS_MAX
> FROM intervaldetail id
> INNER JOIN TreatmentLevel tl ON id.RunAnimalID = tl.RunAnimalID
> INNER JOIN Treatment t on t.TreatmentID = tl.TreatmentID
> WHERE id.intervalsetid = 99
> ORDER BY t.Name, tl.Level, id.sequencenumber
> <END SQL QUERY>
> -- Thanks, Jeff
