Untitled query #48299

SQL

USE bnwiki_p;

-- Re-format wikitable: `(\d+)\.0\|\|(\S[^|]+)`
-- Replace: `$1 || [[User:$2|$2]] `

SELECT actor_name, thanks_count
, thanks_count_i as thanks_count_intial
, (thanks_count + thanks_count_i) as thanks_count_total
FROM
-- next thanks
(
  SELECT actor_name, thanks_count, IFNULL(thanks_count_i, 0) as thanks_count_i
  FROM
  (
    SELECT count(*) as thanks_count, log_actor
    FROM logging_logindex 
    WHERE log_type = 'thanks' AND log_action = 'thanks'
    GROUP BY log_actor
  ) r_next
  -- add initial thanks
  LEFT JOIN
  (
    SELECT count(*) as thanks_count_i, log_actor as log_actor_i
    FROM logging_logindex
    WHERE log_type = 'thanks' AND log_action = 'thanks-i'
    GROUP BY log_actor
  ) r_initial ON log_actor_i = log_actor
  -- actor_name  
  LEFT JOIN actor_logging a ON actor_id = log_actor
) top_thanks
ORDER BY thanks_count DESC, thanks_count_i DESC
SQL

Checking query status...
