Select Users with most combined PostHistory, Comments and Votes

0

Please login or register to vote for this query.

(click on this box to dismiss)

Joomla

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
ORDER BY activity DESC;

Enter Parameters

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