computed 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 2000 Forums

Transact-SQL (2000)

computed column

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2009-03-13 : 00:51:42

hi all,
i have two tables. In tbl1 a column "nou" and in tbl2 column "qty" and "p_id".
eg

p_id qty nou
a001 1 2
a001 2 3
a001 3 5
a001 2 4
a002 2 3
a002 3 4
and i need the following output

p_id qty nou total
a001 1 2 30
a001 2 3 30
a001 3 5 30
a001 2 4 30
a002 2 3 18
a002 3 4 18
the column total is computed column (1*2+2*3+3*5+2*4) where p_id=a001
and (2*3+3*4) where p_id=a002

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 03:50:37

31, not 30.

DECLARE	@Sample TABLE
	(
		p_id VARCHAR(4),
		qty INT,
		nou INT
	)

INSERT	@Sample
SELECT	'a001', 1, 2 UNION ALL
SELECT	'a001', 2, 3 UNION ALL
SELECT	'a001', 3, 5 UNION ALL
SELECT	'a001', 2, 4 UNION ALL
SELECT	'a002', 2, 3 UNION ALL
SELECT	'a002', 3, 4

SELECT	s.p_id,
	s.qty,
	s.nou,
	(SELECT SUM(x.qty * x.nou) FROM @Sample AS x WHERE x.p_id = s.p_id)
FROM	@Sample AS s

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources