Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Nested SQL Query - Count

Posted on 2014-09-11
5
Medium Priority
?
286 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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