Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cumulative total column wise Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 9:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 3:14 PM
Points: 114, Visits: 419
I have an issue where I need to count the cumulative total column wise effectively and could not get a thought of it, I have posted on SQL server central.com but thought if you have an idea on this: I know you have lots to do so this is just when you want to get fresh : or you are more than welcome to avoid it.

I have currently dataset abosulate total) and I need the one in second line:

Opco Region Country Jan Feb Mar
Absolute total OP1 EMEA GB 0.3 1.3 0.7
Cumulative total OP1 EMEA gb 0.3 1.6 2.3

Regards
Meghna
Post #1491850
Posted Thursday, September 5, 2013 9:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 12,994, Visits: 12,409
meghna.bhargav (9/5/2013)
I have an issue where I need to count the cumulative total column wise effectively and could not get a thought of it, I have posted on SQL server central.com but thought if you have an idea on this: I know you have lots to do so this is just when you want to get fresh : or you are more than welcome to avoid it.

I have currently dataset abosulate total) and I need the one in second line:

Opco Region Country Jan Feb Mar
Absolute total OP1 EMEA GB 0.3 1.3 0.7
Cumulative total OP1 EMEA gb 0.3 1.6 2.3

Regards
Meghna


There is nowhere near enough information here to provide even a direction. Please try to explain more clearly what you are trying to. Also, please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1491864
Posted Thursday, September 5, 2013 9:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 861, Visits: 7,270
Not a solution, just a clarification, you need the cumulative total adding each month to the previous month like


select opco, region, country, jan as [jan],
jan + feb as [feb],
jan + feb + mar as [mar],
etc...






And then again, I might be wrong ...
David Webb
Post #1491867
Posted Thursday, September 5, 2013 9:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 3:14 PM
Points: 114, Visits: 419
Yes you got me right , I need that but in very efficient way.

Post #1491871
Posted Thursday, September 5, 2013 10:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 861, Visits: 7,270
If you're not doing any other summation or grouping, I'm not sure you'll find anything more efficient than the straight 'select' in my question. It would only touch the absolute total row once and the cumulative math has to be done somewhere. If there is other summation required, I'd have to know what that was in order to give you a solution.

The more advanced folks here may be able to give you a better solution, so I'll sit back and wait to (once again) learn something.





And then again, I might be wrong ...
David Webb
Post #1491874
Posted Thursday, September 5, 2013 10:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 12,994, Visits: 12,409
meghna.bhargav (9/5/2013)
Yes you got me right , I need that but in very efficient way.



Give us the details to work with and we can help. I am glad David was able to decipher your requirements but without the details there is little we can do here.

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1491879
Posted Friday, September 6, 2013 1:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 3:14 PM
Points: 114, Visits: 419
Ok so I have read the article and also did try to explain which can help me to get the solution:

I have a table :
CREATE table Statistics
(
Status Varchar(10),
Opco Varchar(30),
Region Varchar(30),
Country Varchar(30)
Jan Decimal(3,2),
Feb Decimal(3,2),
Mar Decimal(3,2),
…….
…….

…….
)

Inserting data into the table:

Insert into Statistics
(
‘Absolute total’,
‘OPCO1’,
‘EMEA’,
‘GB’,
‘0.3’,
‘1.3’,
‘0.7’
)

Out put I require is the second row calculating the cumulative totals as shown below:

Absolute total OPCO1 EMEA GB 0.3 1.3 0.7
Cumulative total OPCO1 EMEA GB 0.3 1.6 2.3
Post #1492064
Posted Friday, September 6, 2013 8:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 12,994, Visits: 12,409
You might have read the article but you missed the point. The idea is to provide tables and data in a consumable format. That means that the volunteers around here trying to help should be able to hit f5 and have a table populated with data. That way we can spend our time working on your issue instead of setting up the problem.

Here is an example based on what you posted.

if OBJECT_ID('tempdb..#Statistics') is not null
drop table #Statistics

CREATE table #Statistics
(
Status Varchar(30),
Opco Varchar(30),
Region Varchar(30),
Country Varchar(30),
Jan Decimal(3,2),
Feb Decimal(3,2),
Mar Decimal(3,2)
)

Insert #Statistics
select
'Absolute total',
'OPCO1',
'EMEA',
'GB',
0.3,
1.3,
0.7

However, I have to agree with David. I don't think you are going to find much of a more efficient method of this than what he posted already. I can come up with some really convoluted ways of doing this but honestly what he posted is the simplest.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1492261
Posted Friday, September 6, 2013 8:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 3:14 PM
Points: 114, Visits: 419
I do understand what you mean! But I am in middle of Live issue thinking if i post here, I can get solution.

When you start typing the issue in the message box, it does not take table format nor space formatting as it is in word or outlook.

Anyways, Thanks for taking time on this.

Meghna
Post #1492264
Posted Friday, September 6, 2013 10:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 12,994, Visits: 12,409
meghna.bhargav (9/6/2013)
I do understand what you mean! But I am in middle of Live issue thinking if i post here, I can get solution.

When you start typing the issue in the message box, it does not take table format nor space formatting as it is in word or outlook.

Anyways, Thanks for taking time on this.

Meghna


If you use the IFCode shortcuts (over on the left when posting) it will keep your code cleanly formatted. You should be typing your code in SSMS instead of Word. You can't test your script in there.

I understand you are dealing with a production issue. This is all the more reason to make it easy for people. You will get a LOT more people willing to help when your post is easy to use.

I apologize if you think I am being a pain, I am trying to help you understand the best way to get the assistance that you need.

Are you good now with your issue or do still need some help?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1492340
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse