How do I create a pivot table that shows sum of like-items from the primary table and be able to filter the pivot table and appends calculation on the chosen dates?
I have a database table (orders table) that is densely populated with order information as it happens from users with their ids, and I want to get the sum of fees based on different currencies, and render at the frontend in a tabular form, and I don't even know how to go about it. Below is an example of the table before I proceed with the question:
https://i.stack.imgur.com/sTFTN.png
So I said, I want to get the sum of fees based on different currencies, and render at the frontend in a tabular form, and be able to filter the table based on date, such that when I choose a date rame from a date to a date, the table append to sum of available transactions based on the currency in that particular timeframe.
Example of what I want from the image above is below, and with opportunity to be able to filter with datetime and the calculation of sum happens for only the selected date. All am doing is more of a report solution.
Hey Afolabi,
It should be as simple as using SQLs group and sum functionality. For example:
Order.where("created_at BETWEEN ? AND ?", 1.month.ago, Time.zone.now)group(:currency).sum(:charged_fee)
{"CAD"=>82, "NGN"=>20, "USD"=>143, "YEN"=>40}
Order.where("created_at BETWEEN ? AND ?", 1.month.ago, Time.zone.now)group(:currency).sum(:network_fee)
{"CAD"=>"23", "NGN"=>"40", "USD"=>"60", "YEN"=>"30"}