Counting a field using UNION - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Counting a field using UNION

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-04-13 : 11:00:03

Hello
I’m trying to count an occurrence of field using union command.
The field called Source_Name and is held in the Source Table, The unique Account ID comes from the Account Table and the count needs to come from two other tables, Table1 & Table2 using the field Status which occurs in both tables.
My query so far is returning the correct results using union:

SELECT
A.ACCOUNTID,
S.LEAD,
T1.STATUS,
T1.STATUSDATE
FROM ACCOUNT A
LEFT JOIN Source on S.LEADID=A.LEADID
LEFT JOIN Table1 T1 on T1.ACCOUNTID=A.ACCOUNTID
WHERE t1.Status = ‘Ready’

UNION
SELECT
A.ACCOUNTID,
S.LEAD,
T2.STATUS,
T2.STATUSDATE
FROM ACCOUNT A
LEFT JOIN Source on S.LEADID=A.LEADID
LEFT JOIN Table2 T2 on T2.ACCOUNTID=A.ACCOUNTID
WHERE t2.Status = ‘Ready’

What I need to do is count the number of accounts for Each lead.

I’ve added the count to each select statement but when I union the two count Leads I get the lead appearing twice one for each select statement.

I want the count for each individual lead across the two select statements. I’m sure it’s an easy one but I just can’t get my head around it.

Thanks in advance

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-13 : 11:11:34

use a derived table. Something like


SELECT
	rep.LEAD
	, COUNT(rep.ACCOUNTID) AS [ACCOUNTS]
FROM
	(
	SELECT
		A.ACCOUNTID AS ACCOUNTID,
		S.LEAD AS LEAD,
		T1.STATUS AS STATUS,
		T1.STATUSDATE AS STATUSDATE
	FROM
		ACCOUNT A
		LEFT JOIN Source on S.LEADID=A.LEADID
		LEFT JOIN Table1 T1 on T1.ACCOUNTID=A.ACCOUNTID
	WHERE
		t1.Status = ‘Ready’

	UNION SELECT
		A.ACCOUNTID,
		S.LEAD,
		T2.STATUS,
		T2.STATUSDATE
	FROM
		ACCOUNT A
		LEFT JOIN Source on S.LEADID=A.LEADID
		LEFT JOIN Table2 T2 on T2.ACCOUNTID=A.ACCOUNTID
	WHERE
		t2.Status = ‘Ready’
	)
	rep
GROUP BY
	rep.LEAD

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-13 : 11:29:00

And remember the subtle difference between UNION & UNION ALL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources