Untitled query #48292

SQL

USE bnwiki_p;

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

SELECT actor_name, patrol_count
, patrol_count_i as patrol_count_intial
, (patrol_count + patrol_count_i) as patrol_count_total
FROM
-- next patrols
(
  SELECT actor_name, patrol_count, IFNULL(patrol_count_i, 0) as patrol_count_i
  FROM
  (
    SELECT count(*) as patrol_count, log_actor
    FROM logging_logindex 
    WHERE log_type = 'patrol' AND log_action = 'patrol'
    GROUP BY log_actor
  ) r_next
  -- add initial patrol
  LEFT JOIN
  (
    SELECT count(*) as patrol_count_i, log_actor as log_actor_i
    FROM logging_logindex
    WHERE log_type = 'patrol' AND log_action = 'patrol-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_patrollers
ORDER BY patrol_count DESC, patrol_count_i DESC
SQL

Checking query status...
