Solved

Nested SQL Query - Count

Posted on 2014-09-11
5
275 Views
Last Modified: 2014-09-12
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

0
Comment
Question by:appliedimg
5 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
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
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
Comment Utility
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
 
LVL 6

Expert Comment

by:BALMUKUND KESHAV
Comment Utility
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
 

Author Comment

by:appliedimg
Comment Utility
@ 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
 

Author Closing Comment

by:appliedimg
Comment Utility
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now