Solved

Nested SQL Query - Count

Posted on 2014-09-11
5
279 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
ID: 40318161
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
ID: 40318282
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
ID: 40318592
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
ID: 40319262
@ 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
ID: 40319267
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query 8 50
SSRS  - Dropdown with Null 3 25
MySQL Memory Keeps Increasing 4 35
How to resolve SQL Server DB deadlock which makes my application hangs ? 6 27
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

772 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