January 20, 2011 at 1:48 pm
Ok, here's what I'm trying to do and I don't know if it can be done.
Declare @Variable1 int = 0
Column1 is an int, ie. 2,3,89,2783
Column0 is a date, ie '2011-01-01'
select column0, column1, @Variable1 = Variable1 + column1 as CumulativeSum from table_whatever
group by column0
I run into several issues:
@Variable1 = assignation won't display.. that's obvious
@Variable1 + column1 .. A SELECT statement that assigns a value to a variable cannot not be combined with data-retrieval operations.
Is there a way to do this or is there a cumulative sum operation somewhere that I can use?
Donalith
January 20, 2011 at 1:59 pm
Yes, you can do what you are after. Looks like you have stumbled upon the "Quirky Update" scenario. Google search it, or read about it here -
January 20, 2011 at 1:59 pm
the pseudo code doesn't cut it for this one, i think. We'd need to see the actual statement you are executing, that is raising the error, so we can really help you.
i'm thinking you want the total, and not assign the variable?
select column0, column1, @Variable1 + column1 as CumulativeSum from table_whatever
Lowell
January 20, 2011 at 2:02 pm
Still a work in progress so don't hate on me too much.
declare @Startdate datetime = '2011-01-16'
declare @EndDate datetime = '2011-01-19'
Declare @TotalUpdates int = 0
Declare @WebUpdates int = 0
Declare @TotalUpdateMsg int = 0
Declare @SMSUpdates int = 0
set @TotalUpdates = (select COUNT(cmdtypeid) from MessagesIn where CmdTypeID = 9 and CreatedDate < DATEADD (d,1,@startdate))
set @WebUpdates = (select COUNT(cmdtypeid) from MessagesIn where CmdTypeID = 18 and CreatedDate < DATEADD (d,1,@startdate))
set @TotalUpdates = @TotalUpdates + @WebUpdates
set @TotalUpdateMsg = (select COUNT(cmdtypeid) from MessagesIn where (CmdTypeID = 9 or CmdTypeID =18) and CreatedDate < DATEADD (d,1,@startdate))
select SMS.ReportDate,
SMS.SMSUpdates as Updates_SMS,
MO.SMSUpdatesSent as Update_Msg_SMS,
MW.WebUpdates as Updates_Web,
MO.WebUpdatesSent as Update_Msg_Web,
@TotalUpdates = @TotalUpdates + SMS.SMSUpdates as Total_Updates,
MO.SMSUpdatesSent + MO.WebUpdatesSent as Total_Update_Msg
from
(SELECt cast(dates.ReportDate as date) as ReportDate,
sum(case when MI.CmdTypeID = 9 then 1 else 0 end) as SMSUpdates
from MessagesIn MI
RIGHT OUTER JOIN
(SELECT t.N-1+@StartDate AS ReportDate
FROM dbo.tally t
WHERE t.N-1+@StartDate <= @EndDate) dates
ON cast(MI.CreatedDate as date) = cast(dates.ReportDate as date)
group by dates.ReportDate) SMS
LEFT JOIN
(select cast(createddate as DATE) as ReportDate,
sum(case when CmdTypeID = 9 then 1 else 0 end) as SMSUpdatesSent,
sum(case when CmdTypeID = 18 then 1 else 0 end) as WebUpdatesSent
from MessagesOut
group by CAST(createddate as DATE)) MO
on MO.ReportDate = SMS.ReportDate
LEFT JOIN
(select cast(MW.CreatedDate as DATE) as ReportDate,
sum(case when MW.CmdTypeID = 18 then 1 else 0 end) as WebUpdates
from MessagesWeb MW
group by cast(MW.CreatedDate as DATE)) MW
on MW.ReportDate = SMS.ReportDate
January 20, 2011 at 2:04 pm
If I use that, Lowell, then I only get the total for that row and not the cumulative aggregated total as the rows are retrieved.
Don
January 20, 2011 at 2:11 pm
doh rereading based on getoffmyfoot's comments, you are definitely looking for a running total kind of solution.
Lowell
January 20, 2011 at 2:16 pm
Right. I'm hoping to do it in the original select statement without building a temp table or reseting and inserting into a static report table.
January 20, 2011 at 4:49 pm
Your original post had an invalid GROUP BY scenario so I took the liberty of summing Column1. This solution will perform poorly for large datasets, but is an option using only one query:
if object_id(N'tempdb..#tbl') > 0
drop table #tbl
go
create table #tbl (Column1 int, Column0 datetime)
go
insert into #tbl
select 10,getdate()
union select 1,getdate()
union select 2,getdate()
union select 3,getdate()
union select 4,getdate()
union select 1,getdate()+1
union select 2,getdate()+1
union select 3,getdate()+1
union select 4,getdate()+1
union select 1,getdate()+2
union select 2,getdate()+2
union select 3,getdate()+2
union select 7,getdate()+2
union select 1,getdate()+3
union select 2,getdate()+3
union select 3,getdate()+3
union select 9,getdate()+3
;
go
with cte as (
select
row_number() over (order by Column0) as id,
sum(Column1) as Column1_sum,
Column0
from #tbl
group by Column0
)
select cte1.Column0, cte1.Column1_sum, (select sum(Column1_sum) from cte where id <= cte1.id) as Column1_cumulative_sum
from cte cte1
order by Column0;
--select column0, column1, @Variable1 = Variable1 + column1 as CumulativeSum from table_whatever group by column0
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 21, 2011 at 9:16 am
Thank you. I thought of using the "with cte " formula but I'm working with a dataset of over 700 million rows.
January 21, 2011 at 9:48 am
Donalith (1/21/2011)
Thank you. I thought of using the "with cte " formula but I'm working with a dataset of over 700 million rows.
Then you'll have to do with Jeff's article (link a couple posts above).
January 21, 2011 at 9:51 am
For that large of a dataset a loop structure will likely yield the best performance. If you have an application layer I would recommend doing this type of work there...otherwise a CURSOR can get the job done directly in the database layer.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 21, 2011 at 9:55 am
opc.three (1/21/2011)
For that large of a dataset a loop structure will likely yield the best performance. If you have an application layer I would recommend doing this type of work there...otherwise a CURSOR can get the job done directly in the database layer.
How can I say this politely??
F**K NO seems a little weak. I suggest you also read Jeff's article on the quirky update.
January 21, 2011 at 10:25 am
Yeah, I think the quirky update is probably my most efficient solution. Thanks everyone for your answers.
January 21, 2011 at 10:37 am
CELKO (1/21/2011)
You better off doing the running totals in the front end in T-SQL. Do you have a report writer of some kind?In Standard SQL there is an optional [ROWS|RANGE] clasue that does this beautifully, but we don't have it in the lesser SQLs:
SUM(sales_amt)
OVER (PARTITION BY dept_nbr
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS sale_amt_running_tot
Hmm I can't seem to make this work. Do you have a working exemple?
January 21, 2011 at 10:54 am
Ninja's_RGR'us (1/21/2011)
opc.three (1/21/2011)
For that large of a dataset a loop structure will likely yield the best performance. If you have an application layer I would recommend doing this type of work there...otherwise a CURSOR can get the job done directly in the database layer.How can I say this politely??
F**K NO seems a little weak. I suggest you also read Jeff's article on the quirky update.
Thank you for being polite.
Donalith (1/20/2011)
Right. I'm hoping to do it in the original select statement without building a temp table or reseting and inserting into a static report table.
I was trying to adhere to the above requirement of the poster...calculating the "running total" outside the database, a function most report writers have built into their product, could satisfy the requirement and deliver solid performance.
The "quirky update" method is an interesting option but I for one still have some "healthy skepticism" towards the technique given it's long list of rules. There is no point in being a jerk Ninja's_RGR'us.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply