Add the columns values and put the values in one of the row of the same column

Question

Post reply

Add the columns values and put the values in one of the row of the same column

preensheen

Ten Centuries

Points: 1362
More actions
September 29, 2010 at 12:28 pm

#238927

Hi All,

I have the data as shown below

id value

1 0

2 1

3 1

4 1

5 1

6 1

if the above is the input from sql..say I want the output in csv and the out should look like this

id Value

1 5 (sum of 2 to 6 id's)

2 0

3 0

4 0

5 0

6 0

Could anyone give me any idea's...we can also say id "1 " is of different type and id's from 2 to 6 are of different type.

Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would you please clarify your requirement? What would be the criteria to do the aggregation?

Based on your rather vague example it seems like you want to count the rows with a consecutive value of 1 after a row with a value of Zero. Just guessing...

But in that case it wouldn't be "sum of 2 to 5 id's" but sum of id=2 to id=6.

Side note: Are the values in the id column guaranteed to have no gaps?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

preensheen Ten Centuries Points: 1362 More actions · Answer 2

Hey You are right it is sum of 2 to 6. This is a example of what I have to do?

more detail

idtype id value

A 1 0

B 2 1

B 3 1

B 4 1

B 5 1

B 6 1

we have to sum all the values of type B and then add with value is type A and put the result in the value of type A and make all the values of type b to ZERO

And the id columns make have gap

LutzM SSC Guru Points: 107049 More actions · Answer 3

Something like this?

DECLARE @tbl TABLE



(

   idtype CHAR(1), id INT, VALUE INT

 )

INSERT INTO @tbl

SELECT 'A', 1 ,0 UNION ALL

SELECT 'B', 2 ,1 UNION ALL

SELECT 'B', 3 ,1 UNION ALL

SELECT 'B', 4 ,1 UNION ALL

SELECT 'B', 5 ,1 UNION ALL

SELECT 'B', 6, 1

;

WITH cte AS

(

SELECT SUM(VALUE) AS total

FROM @tbl

WHERE idtype ='B'

)

SELECT

	idtype,

	CASE WHEN idtype ='A' THEN total ELSE 0 END AS new_value

FROM   @tbl

CROSS JOIN cte

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]