Need help on joining 2 select count results

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 2000 Forums

Transact-SQL (2000)

Need help on joining 2 select count results

Author

Topic

delta1186
Starting Member

10 Posts

Posted - 2007-09-26 : 14:16:52

I have two SQL select statements that return the same number of rows, but each statements main result is a count value. What I am trying to figure out is how to display the two results as one record set.

SELECT COUNT(intBidderID) as [Yes Bidders],intBidPackageID,intEstimateID
FROM tblCallList
INNER JOIN LU_tblStatus_Bidder ON tblCallList.intBidderStatusID=LU_tblStatus_Bidder.intBidderStatusID
WHERE LU_tblStatus_Bidder.strBidderStatus = 'Yes'
GROUP BY intBidPackageID,intEstimateID

Yes, BP, Est
1, 1, 136
3, 7, 136
2, 7, 214

SELECT COUNT(tblCL_CallLog.intCallListID) as [Calls],intBidPackageID,intEstimateID
FROM tblCL_CallLog
INNER JOIN tblCallList ON tblCL_CallLog.intCallListID = tblCallList.intCallListID
GROUP BY intBidPackageID,intEstimateID

Calls, BP, Est
1, 1, 136
16, 7, 136
1, 7, 214

See how they result rows are the related by intBidPackageID and EstimateID. What I want is something like this:

Calls, Yes, BP, Est
1, 1, 1, 136
16, 3, 7, 136
1, 2, 7, 214
add of course it will still show records if no results for one of the counts:
Calls, Yes, BP, Est
1, 0, 1, 214
The Yes value might return as NULL.

Thanks,
Kevin

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-26 : 14:23:56

Put the results if first SELECT into a table variable (which has an additional column for Calls). Then do an update on the table variable and insert the Calls value. do a final SELECT from the table variable.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:27:19

[code]SELECT SUM([Yes Bidders]) AS [Yes Bidders],
SUM([Calls]) AS [Calls],
intBidPackageID,
intEstimateID
FROM (
SELECT 1 AS [Yes Bidders],
0 AS [Calls],
intBidPackageID,
intEstimateID
FROM tblCallList
INNER JOIN LU_tblStatus_Bidder ON LU_tblStatus_Bidder.intBidderStatusID = tblCallList.intBidderStatusID
WHERE LU_tblStatus_Bidder.strBidderStatus = 'Yes'

UNION ALL

SELECT 0 AS [Yes Bidders],
1 AS [Calls],
intBidPackageID,
intEstimateID
FROM tblCL_CallLog
INNER JOIN tblCallList ON tblCallList.intCallListID = tblCL_CallLog.intCallListID
) AS d
GROUP BY intBidPackageID,
intEstimateID[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:29:51

If either intBidderID or tblCL_CallLog.intCallListID can be NULL, use

SELECT		COUNT([Yes Bidders]) AS [Yes Bidders],
		COUNT([Calls]) AS [Calls],
		intBidPackageID,
		intEstimateID 
FROM		(		
			SELECT		intBidderID AS [Yes Bidders],
					NULL AS [Calls],
					intBidPackageID,
					intEstimateID 
			FROM		tblCallList 
			INNER JOIN	LU_tblStatus_Bidder ON LU_tblStatus_Bidder.intBidderStatusID = tblCallList.intBidderStatusID
			WHERE		LU_tblStatus_Bidder.strBidderStatus = 'Yes'

			UNION ALL

			SELECT		NULL AS [Yes Bidders],
					tblCL_CallLog.intCallListID AS [Calls],
					intBidPackageID,
					intEstimateID 
			FROM		tblCL_CallLog
			INNER JOIN	tblCallList ON tblCallList.intCallListID = tblCL_CallLog.intCallListID
		) AS d
GROUP BY	intBidPackageID,
		intEstimateID

E 12°55'05.25"
N 56°04'39.16"

delta1186
Starting Member

10 Posts

Posted - 2007-09-26 : 14:31:47

I was thinking I might need to do that. I have created temp sql tables in memory before for handling stored procedure input variables that are comma seperated lists for INSERT statements. So with your suggestion, I will have to create a store procedure to perform this operation. I will give it a try.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:34:27

quote:
Originally posted by dinakar

Put the results if first SELECT into a table variable (which has an additional column for Calls). Then do an update on the table variable and insert the Calls value. do a final SELECT from the table variable.

This will not work if second query have more combinations of intBidPackageID and intEstimateID than first query!

E 12°55'05.25"
N 56°04'39.16"

delta1186
Starting Member

10 Posts

Posted - 2007-09-26 : 14:36:21

Peso, that worked great! Thanks, I will have to study what you did. I am pretty sure I was over thinking the problem. Thanks!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources