Auto Increment Column

[vbdotnetlover]

Hello

Sqlserver MyTable has following data

Vou---------date---------qty
1-------25/12/2009------5
2-------25/12/2009------4
1-------26/12/2009------8
2-------26/12/2009------3
3-------26/12/2009------2

str = "SELECT date, SUM(Qty) AS Qty, FROM mytable GROUP BY Date"
dt = GetTable(str)

These codes display following result

date--------------qty
25/12/2009------9
26/12/2009------13

Now I want to add sno column in result as

Sno--------date---------qty
1------25/12/2009------9
2------26/12/2009------13

What command should in str command to get above result

Please help

[padtes]

Assuming you want order by date (SQL server 2005 or higher)

select row_number() over (order by [date]) as serNo, * from (
SELECT [date], SUM(Qty) AS Qty, FROM mytable GROUP BY [Date] ) t

For SQL 2000, stored proc with temp table with auto increment column

create table #tmp (sNo int identity(1,1), dt dateTime, qty numeric(12,2) )
insert into #tmp (dt, qty) SELECT [date], SUM(Qty) AS Qty, FROM mytable GROUP BY [Date] 
select * from #tmp 
drop table #tmp

please mark as solved if this helps. If you get other solution, post it here.

[achieveSenthil]

Hello

Sqlserver MyTable has following data

Vou---------date---------qty
1-------25/12/2009------5
2-------25/12/2009------4
1-------26/12/2009------8
2-------26/12/2009------3
3-------26/12/2009------2

str = "SELECT date, SUM(Qty) AS Qty, FROM mytable GROUP BY Date"
dt = GetTable(str)

These codes display following result

date--------------qty
25/12/2009------9
26/12/2009------13

Now I want to add sno column in result as

Sno--------date---------qty
1------25/12/2009------9
2------26/12/2009------13

What command should in str command to get above result

Please help

first You update ur table using update query or alter table
Alter table table_Name add column sno int

after that You use this query For Update

Update table_name set sno=1 where qty=9

select Sno, Date , sum(qty) as Qty from Mytable groupr by date