Example Queries
Below are some comon queries that can be used on any Funraisin site either via the page builder with custom queries or directly using pure code.
Donor Leaderboards
Fundraiser Leaderboards
Below are a collection of commonly used leaderboard queries for top amounts raised.
Top Individuals
SELECT SUM(d_amount - d_refund_amount) as SubT,a.member_id,a.m_name_alt,a.m_fname,a.m_lname,a.m_lname_prefix,a.m_username,a.m_company,b.history_id,b.event_id,c.event_name,c.event_key,a.m_photo,b.m_target,b.m_event_photo,c.event_default_image,c.event_type,c.created_member_id,b.m_page_title
FROM members AS a
LEFT JOIN members_history AS b ON a.member_id=b.member_id
LEFT JOIN events AS c ON b.event_id=c.event_id
LEFT JOIN donations AS e ON b.history_id=e.history_id
WHERE a.m_status='1' AND b.is_fundraising='Y' AND b.is_active='Y' AND e.d_status='paid'
GROUP BY b.member_id
ORDER BY SubT DESC,b.member_id ASC
Top Teams
select SUM(d_amount - d_refund_amount) as SubT,b.t_name,b.team_id,b.t_photo,b.event_id,b.t_url,c.event_name,c.event_key,c.event_id,b.t_target,c.event_default_image FROM donations AS a, teams AS b,events AS c WHERE a.team_id=b.team_id AND b.event_id=c.event_id AND a.d_status='paid' AND b.t_status=1 $WHERE GROUP BY a.team_id ORDER BY SubT DESC
Top Organisations
select SUM(d_amount - d_refund_amount) as SubT,b.org_name,b.org_id,b.org_photo,b.event_id,b.org_url,c.event_name,c.event_key,c.event_id,b.org_target,c.event_default_image FROM donations AS a, organisations AS b,events AS c WHERE a.org_id=b.org_id AND b.event_id=c.event_id AND a.d_status='paid' AND b.org_status=1 $WHERE GROUP BY a.org_id ORDER BY SubT DESC
Step Leaderboards
Top Individuals
SELECT SUM(e.steps) as SubT,a.member_id,a.m_fname,a.m_lname,a.m_lname_prefix,a.m_username,a.m_name_alt,a.m_company,b.history_id,b.event_id,c.event_name,c.event_key,a.m_photo,b.m_target,b.m_event_photo
FROM members AS a,members_history AS b,members_fitness_activity AS e,events AS c
WHERE a.member_id=b.member_id AND b.history_id=e.history_id AND b.event_id=c.event_id AND a.m_status='1' AND b.is_fundraising='Y' AND b.is_active='Y' AND e.steps > 0
GROUP BY e.history_id
ORDER BY SubT DESC,b.member_id ASC
Top Teams
SELECT SUM(e.steps) as SubT,a.t_name,a.team_id,a.t_photo,a.event_id,a.t_url,c.event_name,c.event_key,c.event_id,a.t_target
FROM teams AS a,members_fitness_activity AS e, events AS c
WHERE a.team_id=e.team_id AND a.event_id=c.event_id AND e.steps > 0
GROUP BY e.team_id
ORDER BY SubT DESC
Top Organisations
SELECT SUM(e.steps) as SubT,a.org_name,a.org_id,a.org_photo,a.event_id,a.org_url,c.event_name,c.event_key,c.event_id,a.org_target
FROM organisations AS a,members_fitness_activity AS e, events AS c
WHERE a.org_id=e.org_id AND a.event_id=c.event_id AND e.steps > 0
GROUP BY e.org_id
ORDER BY SubT DESC
Distance Leaderboards
Note that for distance based queries, distance is stored as metres so you will need to either divide by 1000 to get Kms or 1609.34 to get miles.
Top Individuals
SELECT SUM(e.distance/1000) as SubT,a.member_id,a.m_fname,a.m_lname,a.m_lname_prefix,a.m_username,a.m_name_alt,a.m_company,b.history_id,b.event_id,c.event_name,c.event_key,a.m_photo,b.m_target,b.m_event_photo
FROM members AS a,members_history AS b,members_fitness_activity AS e,events AS c
WHERE a.member_id=b.member_id AND b.history_id=e.history_id AND b.event_id=c.event_id AND a.m_status='1' AND b.is_fundraising='Y' AND b.is_active='Y' AND e.distance > 0
GROUP BY e.history_id
ORDER BY SubT DESC,b.member_id ASC
Top Teams
SELECT SUM(e.distance/1000) as SubT,a.org_name,a.org_id,a.org_photo,a.event_id,a.org_url,c.event_name,c.event_key,c.event_id,a.org_target FROM organisations AS a LEFT JOIN events AS c ON c.event_id=a.event_id LEFT JOIN members_fitness_activity AS e ON a.org_id=e.org_id WHERE a.org_status=1 AND e.steps > 0 GROUP BY e.org_id ORDER BY SubT DESC
Top Organisations
SELECT SUM(e.distance/1000) as SubT,a.org_name,a.org_id,a.org_photo,a.event_id,a.org_url,c.event_name,c.event_key,c.event_id,a.org_target
FROM organisations AS a,members_fitness_activity AS e, events AS c
WHERE a.org_id=e.org_id AND a.event_id=c.event_id AND e.distance > 0
GROUP BY e.org_id
ORDER BY SubT DESC
Duration Leaderboards
Note that for duration based queries, duration is stored as seconds so in order to convert it to hours you need to divide by 60 twice.
Top Individuals
SELECT SUM(e.duration/60/60) as SubT,a.member_id,a.m_fname,a.m_lname,a.m_lname_prefix,a.m_username,a.m_name_alt,a.m_company,b.history_id,b.event_id,c.event_name,c.event_key,a.m_photo,b.m_target,b.m_event_photo
FROM members AS a,members_history AS b,members_fitness_activity AS e,events AS c
WHERE a.member_id=b.member_id AND b.history_id=e.history_id AND b.event_id=c.event_id AND a.m_status='1' AND b.is_fundraising='Y' AND b.is_active='Y' AND e.steps > 0
GROUP BY e.history_id
ORDER BY SubT DESC,b.member_id ASC
Top Teams
SELECT SUM(e.duration/60/60) as SubT,a.org_name,a.org_id,a.org_photo,a.event_id,a.org_url,c.event_name,c.event_key,c.event_id,a.org_target
FROM organisations AS a,members_fitness_activity AS e, events AS c
WHERE a.org_id=e.org_id AND a.event_id=c.event_id AND e.duration > 0
GROUP BY e.org_id
ORDER BY SubT DESC
Top Organisations
SELECT SUM(e.duration/60/60) as SubT,a.t_name,a.team_id,a.t_photo,a.event_id,a.t_url,c.event_name,c.event_key,c.event_id,a.t_target FROM teams AS a LEFT JOIN events AS c ON c.event_id=a.event_id LEFT JOIN members_fitness_activity AS e ON a.team_id=e.team_id WHERE a.t_status=1 AND e.steps > 0 GROUP BY e.team_id ORDER BY SubT DESC
