By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,199 Members | 1,458 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,199 IT Pros & Developers. It's quick & easy.

Count Item function

P: 41
Hi,

I need to count the number of rows in my Item table. The following statement gives me the number i.e. 200.

Select Count(*) As Counter
From Item
Group By Id

How can I number each individual row so that the row will have a number next to it i.e.

Select Count(*) As Counter,[Count Statement] as Number of the Row
From Item
Group By Id

Thanks
Jan 12 '07 #1
Share this Question
Share on Google+
6 Replies


almaz
Expert 100+
P: 168
It's not clear what are you trying to achieve, because your first query should return a number of rows equal to the number of different Id values in your table, not a single value. If following query is not what you want, please post here sample data and expected result.
Expand|Select|Wrap|Line Numbers
  1. Select Count(*) As Counter, Id as Number of the Row
  2. From Item
  3. Group By Id
  4.  
Jan 12 '07 #2

P: 41
Almaz,

Your query gave me the following error:
Incorrect syntax near the keyword 'of'

Some saple data would be :

ID ItemLookupcode Price
----------- ------------------------- ---------------------
2683 PC6950A 11.6600
1280 B17082205 21.1700
1348 B1SD38913 12.9800
1349 B1SD91013 22.8300
2571 P02600 .1772

If you do the Count Statement it gives you the following:

Select Count(*) As NumberItems from Item

NumberItems
-----------
5

What I need if Possible would be the following :

NumberItems ID ItemLookupcode Price
------------------- ----------- ------------------------- ---------------------
1 2683 PC6950A 11.6600
2 1280 B17082205 21.1700
3 1348 B1SD38913 12.9800
4 1349 B1SD91013 22.8300
5 2571 P02600 .1772


Thanks Almaz
Jan 12 '07 #3

almaz
Expert 100+
P: 168
So you are looking for a way to enumerate rows. You can only do it over some sorting. It means that you must define the order in which rows are to be enumerated. For your case ID field can define the order:
Expand|Select|Wrap|Line Numbers
  1. select count(*) As NumberItems, Item.ID, Item.ItemLookupcode, Item.Price
  2. from Item Prev inner join Item on Prev.ID<=Item.ID
  3. group by Item.ID, Item.ItemLookupcode, Item.Price
In SQL Server 2005 there is a much simpler way:
Expand|Select|Wrap|Line Numbers
  1. select row_number() over (order by ID) as NumberItems, *
  2. from Item
Jan 12 '07 #4

iburyak
Expert 100+
P: 1,017
Maybe you can try this:



select id_col = identity(int,1,1), * into #tmp from Item order by ID

select * from #tmp


You can omit order.
Jan 12 '07 #5

P: 41
That is what I needed. Thanks Almaz
Jan 12 '07 #6

P: 41
iburyak,

Thanks, that worked as well.

Thanks
Jan 12 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.