Add 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

General SQL Server Forums

New to SQL Server Programming

Add column

Author

Topic

vyvanand
Starting Member

4 Posts

Posted - 2012-02-09 : 04:16:53

col 1 col 2
row 1 90 90
row 2 23 113
row 3 15 38
row 4 20 35

i need a query for this ??

in this 1st row l get 90 then if we add 90+23=113 in other column and then 23+15=38 so on..what to do

anand

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 04:31:33

First you should tell us on which column you can see if a row is the first or second or ...
because the data in a table has NO reliable order.

No, you're never too old to Yak'n'Roll if you're too young to die.

vyvanand
Starting Member

4 Posts

Posted - 2012-02-09 : 06:56:23

while get pasted it roll over bad & my prob is
c1 c2
r1 90 90
r2 23 113
r3 15 38
r4 20 35

i need to add 90+23 it should be stored in c2(113)
if i add 23+15 from same column c1 it should get store in c2 after 90 in c2...
note : in c2 values should not get inserted...

anand

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-09 : 07:14:19

[code]
CREATE TABLE #myTable99([ID] int, [type] varchar(20), [qty] int)
GO

INSERT INTO #myTable99([ID], [type], [qty])
SELECT 1, 'Purchase', 10 UNION ALL
SELECT 2, 'Purchase', 20 UNION ALL
SELECT 3, 'Sale', 8 UNION ALL
SELECT 4, 'Purchase', 15 UNION ALL
SELECT 5, 'Sale', 2 UNION ALL
SELECT 6, 'Sale', 8
GO

SELECT * FROM #myTable99
GO
SELECT t.[ID], t.[qty], t.[type], xxx.[SUM_qty]
FROM (
SELECT l.[ID]
, SUM(r.[qty]) AS [SUM_qty]
FROM #myTable99 l
INNER JOIN #myTable99 r
ON r.[ID] <= l.[ID]
GROUP BY l.[ID]) AS XXX
INNER JOIN #myTable99 t
ON xxx.[ID] = t.[ID]
ORDER BY t.[id]
GO
DROP TABLE #myTable99
GO
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

vyvanand
Starting Member

4 Posts

Posted - 2012-02-09 : 07:28:27

X002548,
I asked simple query but u sent some mixed up queries and it itself will take 2 days 2 understand so pls i just need exact query..dont create more column & al...

anyhow thanks X002548-user

anand

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-09 : 07:36:08

Did you even cut and paste and TEST The code I sent..it WILL run

And it seems like you want a running total

And I don't think the Query is the thing that's "Mixed up"

Good Luck

Hey, do what ever "Logic" you have in the front end

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-09 : 07:38:25

Is it the language barrier, Ignorance or Arrogance?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-02-09 : 08:35:22

Vyvanand,

I suggest you start by reading the following paying particular attention to the word 'unordered'.

http://en.wikipedia.org/wiki/Relation_(database)

As you can see, you have to tell SQL what order the rows are in.

Here are two examples:

eg1


-- *** Test Data ***
CREATE TABLE #eg1
(
	RowOrder int NOT NULL
	,Value int NOT NULL
);
INSERT INTO #eg1
SELECT 1, 90
UNION ALL SELECT 4, 20
UNION ALL SELECT 3, 15
UNION ALL SELECT 2, 23;
-- *** End Test Data ***

SELECT T1.RowOrder, T1.Value
	,T1.Value + COALESCE(T2.Value, 0) AS ValuePlusPreviousValue
FROM #eg1 T1
	LEFT JOIN #eg1 T2
		ON T2.RowOrder = T1.RowOrder - 1
ORDER BY RowOrder;

eg2

	
-- *** Test Data ***
CREATE TABLE #eg2
(
	ValueDate datetime NOT NULL
	,Value int NOT NULL
);
INSERT INTO #eg2
SELECT '20120103', 90
UNION ALL SELECT '20120101', 23
UNION ALL SELECT '20120115', 20
UNION ALL SELECT '20120110', 15;
-- *** End Test Data ***

WITH OrderedEg
AS
(
	SELECT ValueDate, Value
		,ROW_NUMBER() OVER (ORDER BY ValueDate) AS RowNum
	FROM #eg2
)
SELECT T1.ValueDate, T1.Value
	,T1.Value + COALESCE(T2.Value, 0) AS ValuePlusPreviousValue
FROM OrderedEg T1
	LEFT JOIN OrderedEg T2
		ON T2.RowNum = T1.RowNum - 1
ORDER BY ValueDate;

vyvanand
Starting Member

4 Posts

Posted - 2012-02-10 : 02:38:02

X002548,Ifor

Thanks a lot & i got solution for my query..

x002548,
the language barrier - as my language is different while comparing your country so it will be difficult for you to understand.I will try to make possible you to understand.sry

Thanks

anand

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources