Skip to content

Instantly share code, notes, and snippets.

@elentras
Created Nov 12, 2012
Embed
What would you like to do?
Looking for the best way to request sql stats over years for each sellers from my db
@top_sellers = Turnover.
joins(:seller).
select('SUM(turnovers.amount) AS total_current_year, SUM(turnovers.amount) AS total_last_year, sellers.*').
where('turnovers.year = ?', 2013).
group('turnovers.seller_id').
order('total_current_year DESC').
limit(5)
@worst_sellers = Turnover.
joins(:seller).
select('SUM(turnovers.amount) AS total_current_year, SUM(turnovers.amount) AS total_last_year, sellers.*').
where('turnovers.year = ?', 2013).
group('turnovers.seller_id').
order('total_current_year ASC').
limit(5)
@elentras
Copy link
Author

elentras commented Nov 12, 2012

@turnovers = Turnover.select('turnovers.*, SUM(turnovers.amount) AS total').group(:year).order('year DESC')
result = Turnover.find_by_sql('SELECT SUM(CASE WHEN turnovers.year = 2013 THEN turnovers.amount END) AS total_current_year, SUM(CASE WHEN turnovers.year = 2012 THEN turnovers.amount END) AS total_last_year, sellers.* FROM `turnovers` INNER JOIN `sellers` ON `sellers`.`id` = `turnovers`.`seller_id` GROUP BY turnovers.seller_id ORDER BY total_current_year DESC')

@top_sellers = result[0..4]
@worst_sellers = result.reverse[0..4]

Loading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment