COUNT and GROUP BY multiple columns in different databases

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 

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

The first example gives me two columns, but the first column has the "total total" and the second column is all '0's.
 

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 ')'

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

Select allOpen in new window

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

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'FullList'.

Is "FullList" an Oracle statement?  I'm using MS T-SQL.

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.

Sorry, that thing above is gibberish.

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. :-(

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 

Select allOpen in new window

Oh, except for the ORDER BY

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

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.

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.

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.

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

Select allOpen in new window

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]

Oh!  No and I should get lots.

Use this query to check if you have any records with [ddlActivityType] = 'ARRA'.

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

Sorry I was using 'ARRA' when I should have been using a different parameter.  THe column IS 'ARRA' but the field actually contains "WIA"

Glad you found the problem. Let me know if you have any more questions.

Thank yo very much.

Thanks a lot!