473,236 Members | 1,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,236 software developers and data experts.

Count Item function

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
6 2966
almaz
168 Expert 100+
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
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
168 Expert 100+
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
1,017 Expert 512MB
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
That is what I needed. Thanks Almaz
Jan 12 '07 #6
iburyak,

Thanks, that worked as well.

Thanks
Jan 12 '07 #7

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

Similar topics

22
by: Ling Lee | last post by:
Hi all. I'm trying to write a program that: 1) Ask me what file I want to count number of lines in, and then counts the lines and writes the answear out. 2) I made the first part like this: ...
2
by: Ken | last post by:
The fact that you can not reassign a variable in XSL is an endless source of frustration, causing you to jump through all sorts of non-intuitive hoops. In this case, however, the lack of...
3
by: jason | last post by:
Hello. I've got this simple collection populate code I downloaded from the net (sorry can't find source now) I'm trying to test, but I can't seem to get it to work. Any help would be greatly...
2
by: Praveen Balanagendra via .NET 247 | last post by:
here is the source code private void AddRow() { TableCell tc = new TableCell(); tc.Controls.Add(new LiteralControl("NewRow")); DataGridItem di = new...
3
by: Hyphessobricon | last post by:
Hallo, Indeed, a count of a query with a group by function gives more records than there are and so for-next structures don't function. How is this to be mended. Anyone? Everyone in fact....
2
by: GoCoogs | last post by:
I'm trying to count how many items are in a dynamic collection. This is the code I have so far. *** Begin Code *** Public Class Rule Private _rulevars As RuleVarsCollection Private _rulename...
1
by: AZKing | last post by:
Hi all, I am trying to create a query that will count the number of items in a certain field. For example, the number of cars listed as "Ford" in the USA. I can do it for just one item, but there...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
26
by: Ping | last post by:
Hi, I'm wondering if it is useful to extend the count() method of a list to accept a callable object? What it does should be quite intuitive: count the number of items that the callable returns...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.