Add 'Cumulative' total column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Add 'Cumulative' total column

Author

Topic

Wils85
Starting Member

3 Posts

Posted - 2015-02-26 : 22:40:09

Hi,

Using the below code, how would I add a Cumulative Total column for the GrossTotal by date?

-- declare variables

DECLARE @prodseasonno
table (psn int) insert into @prodseasonno values ('19089') -- insert multiple Prod Season No.

-- create view
SELECT convert(date,order_date), sum(num_seats), sum(GrossTotal)
FROM dbo.LVS_TKT_HIST, @prodseasonno
WHERE prod_season_no = psn
GROUP BY convert(date,order_date)
ORDER BY convert(date,order_date)

Can anyone assist?

Thanks

WS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-27 : 12:17:24

Could you show an example of what the current code returns and then show us what you want it to return?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Wils85
Starting Member

3 Posts

Posted - 2015-03-01 : 06:56:01

Sure.

E.g. The fourth 'CumulativeTotal' column is what I'd like add.

order_date | num_seats | GrossTotal | CumulativeTotal
01/01/2015 | 7 | $210 | $210
02/01/2015 | 3 | $90 | $300

I've tried adding the column using 'SUM(GrossTotal) OVER (ORDER BY convert(date,order_date))' or adjusted this to be 'SUM(GrossTotal) OVER (ORDER BY 'order_date'))', however neither works.

quote:
Originally posted by tkizer

Could you show an example of what the current code returns and then show us what you want it to return?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-01 : 08:35:54

sum(...) over(...rows unbounded preceding)

https://msdn.microsoft.com/en-us/library/ms189461.aspx

Wils85
Starting Member

3 Posts

Posted - 2015-03-01 : 23:30:30

I tried 'rows unbounded proceding' however this did not work either.

-- declare variables

DECLARE @prodseasonno
table (psn int) insert into @prodseasonno values ('19089') -- insert multiple Prod Season No.

-- create view
SELECT convert(date,order_date) as 'Order Date', sum(num_seats) as 'Ticket Sales', sum(GrossTotal) as 'Gross Total', sum(GrossTotal) OVER (PARTITION by 'Order Date' ORDER BY 'Order Date' ROWS UNBOUNDED PRECEDING) as CumulativeTotal
FROM dbo.LVS_TKT_HIST, @prodseasonno
WHERE prod_season_no = psn
GROUP BY convert(date,order_date)
ORDER BY 'Order Date'

I get the following error:
"Msg 8120, Level 16, State 1, Line 7
Column 'dbo.LVS_TKT_HIST.GrossTotal' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

When I remove the code for the column i want, it works fine.

Any other suggestions?

Thanks

W

quote:
Originally posted by gbritton

sum(...) over(...rows unbounded preceding)

https://msdn.microsoft.com/en-us/library/ms189461.aspx

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-02 : 04:19:03

your syntax is incorrect, which is what the error message explains. you need something like

select date, sum(num_seats) over(partition...rows...),
sum(gross total) over(partition...rows...)

remove the group by date. It makes no sense for a cum total by date anyway (think about it)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources