COUNT and GROUP BY multiple columns in different databases

This query gives me the counts I need from columns with the same name in two different databases, but I need the counts in two different columns, one labeled 'ARRA' and the other 'SYEP'.
This gives me only one column labeled 'ARRA' with both counts mixed into it.
Thanks.
SELECT [ZipCode],COUNT(1) AS ARRA
  FROM [SYEP20091203].[dbo].[Applicants]
  WHERE (txtConsidered IS NOT NULL) AND Status = '1'
  GROUP BY [ZipCode]
  UNION
  SELECT [ZipCode],COUNT(1) SYEP
  FROM [SYEP2007].[dbo].[Applicants]
  WHERE (txtConsidered IS NOT NULL) AND Status = '1'
  GROUP BY [ZipCode]
  ORDER BY COUNT(1) DESC

-- Gives me:
ZipCode	ARRA
33311	465
33311	242
33313	172
33313	104
33312	96
33312	85
33060	73
33060	66
33023	54
33023	51

Open in new window

LVL 1
megninAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:

SELECT [ZipCode] ,COUNT(1) AS ARRA, 0 as SYEP 
  FROM [SYEP20091203].[dbo].[Applicants] 
  WHERE (txtConsidered IS NOT NULL) AND Status = '1' 
  GROUP BY [ZipCode] 
  UNION 
  SELECT [ZipCode],0 as ARRA, COUNT(1) AS SYEP 
  FROM [SYEP2007].[dbo].[Applicants] 
  WHERE (txtConsidered IS NOT NULL) AND Status = '1' 
  GROUP BY [ZipCode] 
  ORDER BY COUNT(1) DESC 

Open in new window

Om PrakashCommented:
Working example
create table #Applicants (zipcode int, aa int)
insert into #Applicants values (1112,2)

select zipcode, count(1) as [ARRA], 0 as [SYEP] from #Applicants group by zipcode
union
select zipcode, 0 as [ARRA], count(1) as [SYEP] from #Applicants group by zipcode

Open in new window

Máté FarkasDatabase Developer and AdministratorCommented:
For example:
SELECT * FROM (
SELECT [ZipCode], COUNT(1) AS [Count], 'ARRA' as Db
  FROM [SYEP20091203].[dbo].[Applicants] 
  WHERE (txtConsidered IS NOT NULL) AND Status = '1' 
  GROUP BY [ZipCode] 
  UNION 
  SELECT [ZipCode], COUNT(1) AS [Count], 'SYEP' as Db
  FROM [SYEP2007].[dbo].[Applicants] 
  WHERE (txtConsidered IS NOT NULL) AND Status = '1' 
  GROUP BY [ZipCode] 
) ORDER BY [Count] DESC

Open in new window

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

megninAuthor Commented:
The first example gives me two columns, but the first column has the "total total" and the second column is all '0's.
 
megninAuthor Commented:
aqux3e,  I'm getting a syntax error:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'ORDER'.
I know the parentheses are in the right place.  I remove the ORDER BY and got the same error on ')'
Máté FarkasDatabase Developer and AdministratorCommented:
Or this:
SELECT [ZipCode], [ARRA], [SYEP]
(

SELECT *, 'ARRA' Db FROM [SYEP20091203].[dbo].[Applicants]  
UNION ALL
SELECT *, 'SYEP' Db FROM [SYEP2007].[dbo].[Applicants]

) FullList
PIVOT (COUNT(*) FOR Db IN ([Arra], [SYEP])) P

Open in new window

megninAuthor Commented:
...I'd prefer not to use a "create table" if I can help it...
megninAuthor Commented:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'FullList'.
megninAuthor Commented:
Is "FullList" an Oracle statement?  I'm using MS T-SQL.
megninAuthor Commented:
A friend gave me something like this:

SELECT Z1.*, Z2.SecondQuery
(Select X1.Zipcode, Y1.FirstQuery
FROM UNION JOIN X1
LEFT OUTER JOIN
FirstQuery Y1
ON X1.Zip = Y1.Zip Z1
LEFT OUTER JOIN
SecondQuery Z2
ON Zipcode)
I may have part of it mistyped because I get a syntax error near 'Select' and 'UNION', but it's a different technique.
megninAuthor Commented:
Sorry, that thing above is gibberish.
ThomasianCommented:

--1st method
SELECT [ZipCode]
     , COUNT(CASE WHEN [Type]='ARRA' THEN 1 END) [ARRA]
     , COUNT(CASE WHEN [Type]='SYEP' THEN 1 END) [SYEP]
FROM
  (SELECT [ZipCode], 'ARRA' [Type] FROM [SYEP20091203].[dbo].[Applicants]
   UNION ALL
   SELECT [ZipCode], 'SYEP' [Type] FROM [SYEP2007].[dbo].[Applicants]
  ) T
GROUP BY [ZipCode]
  

--2nd method
SELECT COALESCE(T1.[ZipCode],T2.[ZipCode]) [ZipCode]
     , T1.[ARRA]
     , T2.[SYEP]
FROM
  (SELECT [ZipCode], COUNT(1) [ARRA] FROM [SYEP20091203].[dbo].[Applicants] GROUP BY [ZipCode]) T1 FULL JOIN
  (SELECT [ZipCode], COUNT(1) [SYEP] FROM [SYEP2007].[dbo].[Applicants] GROUP BY [ZipCode]) T2 ON T1.ZipCode=T2.ZipCode 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
megninAuthor Commented:
2nd method is perfect.
If I needed a second cound in the [SYEP20091203] database; [CSC]  and then the counts in descending order what needs to be added.  I tried it myself and got a syntax error. :-(
megninAuthor Commented:
I think I got it:

SELECT COALESCE(T1.[ZipCode],T2.[ZipCode],T3.[ZipCode]) [ZipCode] 
     , T1.[ARRA] 
     , T2.[CSC] 
     , T3.[SYEP] 
FROM 
  (SELECT [ZipCode], COUNT(1) [ARRA] FROM [SYEP20091203].[dbo].[Applicants] GROUP BY [ZipCode]) T1 FULL JOIN 
  (SELECT [ZipCode], COUNT(1) [CSC] FROM [SYEP20091203].[dbo].[Applicants] GROUP BY [ZipCode]) T2 ON T1.ZipCode=T2.ZipCode FULL JOIN 
  (SELECT [ZipCode], COUNT(1) [SYEP] FROM [SYEP2007].[dbo].[Applicants] GROUP BY [ZipCode]) T3 ON T2.ZipCode=T3.ZipCode 

Open in new window

megninAuthor Commented:
Oh, except for the ORDER BY
ThomasianCommented:
Slight modification on your code:

>> ON T2.ZipCode=T3.ZipCode

should be

>> ON T1.ZipCode=T3.ZipCode OR T2.ZipCode=T3.ZipCode

For the ORDER BY, you can just add it at the end of the query followed by the column names/number

i.e.

SELECT ...
FROM ...
ORDER BY [ARRA] DESC, [CSC] DESC, [SYEP] DESC
megninAuthor Commented:
Cool.  I may have to just pick one because I don't think I can order by multiple columns matching the same Zipcodes.  Say 33311 has fewer in one column than in another the orders wouldn't match.  Selecting one is fine, I just need a general idea of the distributions.
This is great.  Thank you.
ThomasianCommented:
You're welcome.

Btw, I used OUTER JOIN based on the assumption that not all ZipCodes exists on all 3 tables. If you are sure that all ZipCodes exists in all 3 tables or you don't need those records returned, then you can use INNER JOIN and there's no need to add the "OR T1.ZipCode=T3.ZipCode" on the join condition.
ThomasianCommented:
Should you decide to use the inner join, you will also not need the "COALESCE(T1. ...) at the SELECT statement. "SELECT T1.ZipCode" will do.
megninAuthor Commented:
This gives me all NULL in the ARRA column.
--2nd method 
SELECT COALESCE(T1.[ZipCode],T2.[ZipCode],T3.[ZipCode]) [ZipCode] 
     , T1.[ARRA] 
     , T2.[CSC] 
     , T3.[SYEP] 
FROM 
  (SELECT [ZipCode], COUNT(1) [ARRA] FROM [SYEP20091203].[dbo].[Applicants] WHERE [ddlActivityType] = 'ARRA'  GROUP BY [ZipCode]) T1 FULL JOIN 
  (SELECT [ZipCode], COUNT(1) [CSC] FROM [SYEP20091203].[dbo].[Applicants] WHERE [ddlActivityType] = 'CSC' GROUP BY [ZipCode]) T2 ON T1.ZipCode=T2.ZipCode FULL JOIN 
  (SELECT [ZipCode], COUNT(1) [SYEP] FROM [SYEP2007].[dbo].[Applicants] GROUP BY [ZipCode]) T3 ON T1.ZipCode=T3.ZipCode OR T2.ZipCode=T3.ZipCode

Open in new window

ThomasianCommented:
If you run this query, do you get any results?

SELECT [ZipCode], COUNT(1) [ARRA] FROM [SYEP20091203].[dbo].[Applicants] WHERE [ddlActivityType] = 'ARRA'  GROUP BY [ZipCode]
megninAuthor Commented:
Oh!  No and I should get lots.
ThomasianCommented:
Use this query to check if you have any records with [ddlActivityType] = 'ARRA'.

SELECT [ZipCode], [ddlActivityType]
FROM [SYEP20091203].[dbo].[Applicants]
ORDER BY [ddlActivityType]

megninAuthor Commented:
Sorry I was using 'ARRA' when I should have been using a different parameter.  THe column IS 'ARRA' but the field actually contains "WIA"
ThomasianCommented:
Glad you found the problem. Let me know if you have any more questions.
megninAuthor Commented:
Thank yo very much.
megninAuthor Commented:
Thanks a lot!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.