jeremy.bruker (11/26/2012)
Well basically I wanted it to return all the license for the state. Then only tell me how many actual pilots I have in those states. So when it comes to the two PP licenses for user 5. I want both of them counted in the state total. However, for pilots in the region. I would only want it to count as 1.So I think it is actually two different queries cases I am thinking about here.
Thank you for your response.
Your original posted desired output does not seem to match your sample data. I think this should be close to what you are looking for.
SELECT Base_State, SUM(case when type = 'PP' then 1 else 0 end) as PP,
SUM(Case when type = 'JP' then 1 else 0 end) as JP,
(select COUNT(distinct UserNumber) from #UserMain u where u.Base_State = a.Base_State) as People
FROM #UserMain a
left JOIN #Professional b ON a.UserNumber = b.UserNumber
group by Base_State
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/