Total # of badges by 'color', for a single user networkwide

2

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250)
                       , class tinyint
                       , [count] int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union all'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , b.class
     , count(*)
from ' + quotename(name) + '.dbo.badges b
inner join ' + quotename(name) + '.dbo.users u on b.userid = u.id
where u.accountid = ' + CAST(##AccountId:int## AS nvarchar) + '
group by b.class
'
from sys.databases
where database_id > 5
-- AccountId: The global account ID of the user, which you can find in the URL of their network profile. Visit this link: http://stackexchange.com/users/current to see your own ID.

-- execute it
exec (@sql)

select CASE class WHEN 1 THEN 'Gold'
                  WHEN 2 THEN 'Silver'
                  WHEN 3 THEN 'Bronze' END,
  sum(count) AS 'Badges'
  from #results
  group by class
  order by class

drop table #results

Enter Parameters

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