How to get the number of closure votes by type?

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackoverflow.com/questions/368277/how-to-get-the-number-of-closure-votes-by-type

Stack Overflow

Q&A for professional and enthusiast programmers

;with closevotes as
(
select 
       case crt.id 
       when 1 then 101
       when 2 then 102
       else crt.id
       end id
     , JSON_value(text, '$.Voters['+ value + '].Id') userid
     , JSON_value(text, '$.Voters['+ value + '].BindingReason.GoldTagBadge') hammertag
from posthistory ph
inner join closereasontypes crt on crt.id = comment
cross join string_split('0,1,2,3,4,5',',')
where ph.posthistorytypeid = 10 
and JSON_value(text, '$.Voters['+ value + '].Id') is not null
and JSON_value(text, '$.Voters['+ value + '].Id') = '##userid:int?8620333##'
)

SELECT hammertag,
      [101] [duplicate],                  
      [102] [off-topic],                  
      [103] [unclear what you're asking], 
      [104] [too broad],                  
      [105] [primarily opinion-based],
     
      [3] [not constructive],           
      [4] [not a real question],        
      [7] [too localized],              
      [10] [general reference],          
      [20] [noise of pointless]
FROM  
    (select id, userid, hammertag from closevotes)   
    AS source  
PIVOT  
(  
    count(userid)  
FOR   
id   
    IN ( [3], [4],[7],[10], [20], [101], [102], [103], [104], [105])  
) AS pivotdata

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results