Need help with the SELECT statement.

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with the SELECT statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-12-04 : 10:22:08

--SQL 2005

I need to give a total count of LCPI from another table based upon the Loanid. Below show the
business rule and desire results. Thank you in advance.

IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
DROP TABLE #LoanInfo
GO

CREATE TABLE #LoanInfo
(
LoanId VARCHAR(8) NULL,
Status VARCHAR(10) NULL
)
GO

IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
DROP TABLE #LoanComp
GO
CREATE TABLE #LoanComp
(
LCPI INT NULL,
LoanId VARCHAR(8) NULL
)
GO

INSERT INTO #LoanInfo VALUES ('102752', 'PortActive')
INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
INSERT INTO #LoanInfo VALUES ('102922', 'PortActive')
INSERT INTO #LoanInfo VALUES ('102983', 'PortActive')
INSERT INTO #LoanInfo VALUES ('104101', 'PortActive')

GO

INSERT INTO #LoanComp VALUES(123079,102922)
INSERT INTO #LoanComp VALUES(123098,102922)
INSERT INTO #LoanComp VALUES(123069,102922)
INSERT INTO #LoanComp VALUES(123034,102922)
INSERT INTO #LoanComp VALUES(146725,102922)
INSERT INTO #LoanComp VALUES(18419, 102922)

INSERT INTO #LoanComp VALUES(122297,102983)
INSERT INTO #LoanComp VALUES(122324,102983)
INSERT INTO #LoanComp VALUES(146774,102983)
INSERT INTO #LoanComp VALUES(122297,102983)
INSERT INTO #LoanComp VALUES(146781,102983)

INSERT INTO #LoanComp VALUES(124626,104101)
INSERT INTO #LoanComp VALUES(124633,104101)
GO

SELECT *
FROM #LoanInfo;

SELECT *
FROM #LoanComp;

Rule: Count number of LCPI from #LoanComp based upon the LoanId associate to #LoanInfo table.
-- Result want:

LoanId Status TotalCountLCPI
-------- ---------- --------------
102752 PortActive 0
102922 PortActive 6
102922 PortActive
102983 PortActive 5
104101 PortActive 2

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-04 : 10:33:02

Any particular reason why '102922' is showing as active twice?

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-04 : 10:37:43

Hi
You expect this.?.


SELECT A.LOANID ,MIN(A.STATUS) as Status ,COUNT(LCPI)as TotalCountLCPI
FROM	  #LOANINFO A,
	  #LOANCOMP  B
WHERE A.LOANID  =	B.LOANID
GROUP BY A.LOANID

-------------------------
R...

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-12-04 : 10:39:11

quote:
Originally posted by DP978

Any particular reason why '102922' is showing as active twice?

No. The results can be return like this. Thanks.

LoanId Status TotalCountLCPI
-------- ---------- --------------
102752 PortActive 0
102922 PortActive 6
102983 PortActive 5
104101 PortActive 2

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-12-04 : 10:42:18

I can't do group by and Min because I have about 50 other columns returns and 4 others tables need to JOINs. This is just a quick test tables. Is there a way to do a count first in #LoanComp based upon the LoanId and return the total count back. Thanks.

quote:
Originally posted by rajdaksha

Hi
You expect this.?.

SELECT A.LOANID ,MIN(A.STATUS) as Status ,COUNT(LCPI)as TotalCountLCPI
FROM #LOANINFO A,
#LOANCOMP B
WHERE A.LOANID = B.LOANID
GROUP BY A.LOANID

-------------------------
R...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-04 : 10:42:25

This?

SELECT a.LoanId,a.Status,coalesce(b.TotalCountLCPI,0)
FROM #LoanInfo a 
left join (SELECT LoanId,COUNT(LCPI) AS TotalCountLCPI from #LoanComp group by LoanId) b
on a.LoanId=b.LoanId

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-04 : 10:43:10

Select li.LoanID , li.Status, Count( lc.LoanID) from (Select distinct * from #LoanInfo) li
LEFT outer JOIN
#LoanComp lc on li.LoanID = lc.LoanID
group by
li.LoanID, li.Status

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources