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  u.location
, count(*) as per_country
from users u 
where u.location is not null
and u.location <> ''
group by u.location
)
, badge_loc as (
select  u.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 <>''
group by u.location 
)
select a.location
     , a.per_country
     , b.[#java badge holders]
     , (select count(*) 
        from users 
        where location is not null
        and location <> '') 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