Nested SQL Query - Count

Hello Everyone,

I'm trying to query two tables to join them on itemnumber. Here are my two tables:

Table 1
ID --> 123
VendorName --> Amazon
VendorNumber --> 1111
Invoice --> 0001

Table2
ID --> 123
GL Code -->
GL Amount


I want to join the two tables on ID - no problem.
However, I want to count the number of rows in table2 (which will reset when ID changes) and display the count along with the join. So the end result will look something like this:

ID1, VendorName1, VendorNumber1, Count(1), GL Code1, GL Amount1
ID1, VendorName1, VendorNumber1, Count(2), GL Code2, GL Amount2
ID2, VendorName2, VendorNumber2, Count(1), GL Code1, GL Amount1
ID2, VendorName2, VendorNumber2, Count(2), GL Code2, GL Amount2
ID2, VendorName2, VendorNumber2, Count(3), GL Code3, GL Amount3
ID3, VendorName3, VendorNumber3, Count(1), GL Code1, GL Amount1
 and so forth....

I started with the following query but can't seem to reset the Count when the ID changes.

There might be some syntax errors in the code below since I did not want to paste the query from my production.

I'd appreciate any help. Thanks.
select
    identity(int,1,1) as Count,
    table2.GLCode as GLCode,
    table2.GLAmount as GLAmount 
into
    #myTable
from   table2, table1 where table1.ID = table2.ID

select table1.ID, table1.VendorName, table1.VendorNumber, temp.Count, temp.GLCode, temp.GLAmount
from table1, #myTable as temp
Where table1.ID = temp.ID

Open in new window

Evan FiddlerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John_VidmarCommented:
SELECT	a.ID
	a.VendorName
	a.VendorNumber
	c.[Count]
	b.GLCode
	b.GLAmount
FROM	table1	a
JOIN	table2	b	ON	a.ID = b.ID
JOIN	(	SELECT	ID
		,	[Count] = count(*)
		FROM	table2
		GROUP
		BY	ID
	)	c	ON	a.ID = c.ID

Open in new window

0
chaauCommented:
To add a "counter" column you need to use a Window Function. Use ROW_COUNT() and partition it over the ID column, like this:
select table1.ID, table1.VendorName, table1.VendorNumber, 
ROW_NUMBER() OVER(PARTITION BY table1.ID ORDER BY table2.GLCode) AS rn, 
table2.GLCode, table2.GLAmount
from table1 INNER JOIN table2
ON table1.ID = temp.ID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BALMUKUND KESHAVCommented:
here you do many to one relation and count the desired field in manyrows table and group by many rows table id field like this :

select b.id,count(b.glcode),a.vendorname from table2 b, table1 a where b.id=a.id group by b.id
0
Evan FiddlerAuthor Commented:
@ John_Vidmar, thanks for your response. I appreciate it. However, the resulting count was not incremental but the count of total rows in table2. Something like this:

ID1, VendorName1, VendorNumber1, 2, GL Code1, GL Amount1
ID1, VendorName1, VendorNumber1, 2, GL Code2, GL Amount2
ID2, VendorName2, VendorNumber2, 3, GL Code1, GL Amount1
ID2, VendorName2, VendorNumber2, 3, GL Code2, GL Amount2
ID2, VendorName2, VendorNumber2, 3, GL Code3, GL Amount3
ID3, VendorName3, VendorNumber3, 1, GL Code1, GL Amount1
0
Evan FiddlerAuthor Commented:
Chaau, your solution worked like a charm. I did have to make some changes to accommodate my tables names, but that was it.

Thanks much for your time!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.