0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Joomla! administrators, users, developers and designers

SELECT TOP 1000
  u.id as [User Link],
  u.id,
  u.location,
  COALESCE(p.activity,0) + COALESCE(c.activity,0) + COALESCE(v.activity, 0) AS activity
FROM users u
LEFT JOIN (
  SELECT userid, COUNT(id) AS activity
  FROM posthistory
  GROUP BY userid
) as p ON ( p.userid = u.id )
LEFT JOIN (
  SELECT userid, COUNT(id) AS activity
  FROM comments
  GROUP BY userid
) as c ON ( c.userid = u.id )
LEFT JOIN (
  SELECT userid, COUNT(id) AS activity
  FROM votes
  GROUP BY userid
) as v ON ( v.userid = u.id )
WHERE u.id > 0 and u.location is not null and u.location LIKE 'china' or u.location LIKE 'China'
ORDER BY activity DESC;

fork query

edited apr 17 19 anonymous

created apr 11 19 garrett