count query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

count query

Author

Topic

inou
Starting Member

9 Posts

Posted - 2012-06-18 : 17:26:14

Could some one help me with a query to do the below.
I have 2 tables

quote:
tblCustomer:
ID1 ID2 Fname Lname
A1 1 Simth M.
A1 2 Bob B.
A2 1 David B.
A3 2 Derek B.
A4 3 Tammy N.
A4 1 Ann J.

tblRequest:
ReID CustID1 CustID2
1 A1 1
2 A1 1
3 A2 1
4 A2 1
5 A3 2
6 A1 1
7 A2 1
8 A4 3
9 A1 2
10 A4 1

How can I get the result below:

quote:

CustID1 CustID2 Fname Lname TotalRequests
A1 1 Smith M. 3
A1 2 Bob B. 1
A2 1 David B. 3
A3 2 Derek B. 1
A4 3 Tammy N. 1
A4 1 Ann J. 1

I have tried:

quote:

SELECT
A.[Fname],
A.[Lname],
A.[ID1],
A.[ID2],
COUNT(B.[CustID1]) AS TotalRequest
FROM
[tblCustomer] AS A
INNER JOIN
[tblRequest] AS B
ON B.[CustID2] = A.[ID2]
WHERE A.[ID1] = B.[CustID1]
GROUP BY
A.[Fname],
A.[Lname],
A.[ID1],
A.[ID2]
ORDER BY
A.[ID2],
A.[ID1]

But not getting the right results.
Please help!
Thanks in advanced.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 22:40:01

[code]
SELECT r.CustID1,r.CustID2,
COUNT(r.ReID) AS TotalRequests,
c.Fname,c.Lname
FROM tblCustomer c
INNER JOIN tblRequest r
ON r.CustID1 = c.ID1
AND r.CustID2 = c.ID2
GROUP BY r.CustID1,r.CustID2,c.Fname,c.Lname
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inou
Starting Member

9 Posts

Posted - 2012-06-19 : 10:59:57

quote:
Originally posted by visakh16

SELECT r.CustID1,r.CustID2,
COUNT(r.ReID) AS TotalRequests,
c.Fname,c.Lname
FROM tblCustomer c
INNER JOIN tblRequest r
ON r.CustID1 = c.ID1
AND r.CustID2 = c.ID2
GROUP BY r.CustID1,r.CustID2,c.Fname,c.Lname

Thanks, My request tbl contents 900K records but it returns only 400K. Something is missing.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-19 : 11:16:24

Perhaps not all customers have requests? Some customers have multiple requests?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 14:05:36

quote:
Originally posted by inou

quote:
Originally posted by visakh16

SELECT r.CustID1,r.CustID2,
COUNT(r.ReID) AS TotalRequests,
c.Fname,c.Lname
FROM tblCustomer c
INNER JOIN tblRequest r
ON r.CustID1 = c.ID1
AND r.CustID2 = c.ID2
GROUP BY r.CustID1,r.CustID2,c.Fname,c.Lname

Thanks, My request tbl contents 900K records but it returns only 400K. Something is missing.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

only you'll able to understand whats missing as we cant see neither know about your data
may be you've only 400K unique customer who raised those 900 K requests

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources