Group By Issue - 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 2008 Forums

Transact-SQL (2008)

Group By Issue

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2014-05-28 : 03:30:12

Hi, I has the following rows in my table.

Item_No Amount
1 100.00
2 300.00
3 500.00

How can I query so that I can Sum the amount of the Item_No that smaller than current row. Such as the result below:

Item_No Amount Total
1 100 100
2 300 400
3 500 900

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-28 : 03:34:10

[code]
;with CTE
AS
(select 1 Item_No, 100.00 Amount union all
select 2, 300.00 union all
select 3, 500.00 )

SELECT
*
FROM
CTE as A
outer apply
(SELECT
SUM(Amount) as Total
FROM
CTE as B
WHERE
A.Item_No>=B.Item_No) B
ORDER BY Item_No
[/code]

output
[code]
Item_No Amount Total
1 100.00 100.00
2 300.00 400.00
3 500.00 900.00
[/code]

sabinWeb MCP

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-28 : 04:39:14

And there is one more way


CREATE TABLE #Temp(Item_No  int,Amount decimal(15,2))
INSERT INTO #Temp VALUES(1,100.00),(2,300.00),(3,500.00)

SELECT	 Item_No
	,Amount
	,CASE WHEN y.Rn =1 THEN Amount ELSE  Amount +(SELECT SUM(Amount) FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Rn  FROM	#Temp)x WHERE x.Rn<y.Rn)
			  END
		 AS Total
FROM(SELECT	 *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Rn 
FROM	#Temp)y

DROP TABLE #Temp

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources