Solved

Nested SQL Query - Count

Posted on 2014-09-11
5
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 25

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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