Hey all im trying to make a system that reads from the users table but orders by the users rank. This isant a field in the user table but is a selection of variables in the activity table.
Im having trouble with making the result return, its a set of events workinng out Captures / Been Captured + Binds / Been Bound + Kills / Been Killed, but its returning values that dont make sence... e.g the user never has a result above 1.999999999 etc.....
It could be a maths issue but im no good at working out ratios....
Here is the COMPLEX SQL statement....
SELECT usr.id, usr.firstname, usr.lastname,
(SELECT COUNT() FROM scs_stat_activity WHERE mode='capture' AND user_id=usr.id) as HaveCap,
(SELECT COUNT() FROM scs_stat_activity WHERE mode='capture' AND target_id=usr.id) as BeenCap,
(SELECT COUNT() FROM scs_stat_activity WHERE mode='bound' AND target_id=usr.id) as HaveBound,
(SELECT COUNT() FROM scs_stat_activity WHERE mode='bound' AND target_id=usr.id) as BeenBound,
(SELECT COUNT() FROM scs_stat_activity WHERE mode='killed' AND target_id=usr.id) as HaveKill,
(SELECT COUNT() FROM scs_stat_activity WHERE mode='killed' AND target_id=usr.id) as BeenKill,
(
((SELECT COUNT() FROM scs_stat_activity WHERE mode='capture' AND user_id=usr.id) / (SELECT COUNT() FROM scs_stat_activity WHERE mode='capture' AND target_id=usr.id))
+
((SELECT COUNT() FROM scs_stat_activity WHERE mode='bound' AND user_id=usr.id) / (SELECT COUNT() FROM scs_stat_activity WHERE mode='bound' AND target_id=usr.id))
+
((SELECT COUNT() FROM scs_stat_activity WHERE mode='killed' AND user_id=usr.id) / (SELECT COUNT() FROM scs_stat_activity WHERE mode='killed' AND target_id=usr.id))
/ 3)
as Rank
FROM
scs_stat_users AS usr
WHERE
id!=7 AND id!=8
ORDER BY Rank DESC
LIMIT 0,10;
