badges per location

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

;with all_loc as (
select case when u.location is null then ''
            else u.location
       end as  location
, count(*) as per_country
from users u 
group by case when u.location is null then ''
            else u.location
       end
)
, badge_loc as (
select case when u.location is null then ''
            else u.location
       end as  location
, count(*) as [#java badge holders]
from users u 
inner join badges b on b.userid = u.id
where b.name ='java'
-- and u.location is not null
-- and u.location not in('','~', '-' )
group by case when u.location is null then ''
            else u.location
       end 
)
select a.location
     , a.per_country
     , b.[#java badge holders]
     , (select count(*) from users) as tot_count
from all_loc a
inner join badge_loc b on b.location = a.location

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results