All threads / 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?

Ask A Question

Notifications

You’re not receiving notifications from this thread.

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?

Afolabi Olaoluwa asked in Rails

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.

https://i.stack.imgur.com/VP5R3.png

@GoRails, can I pay for a turorial on this? I need it urgently and I dont know what to do

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"}
Join the discussion

Want to stay up-to-date with Ruby on Rails?

Join 37,344+ developers who get early access to new tutorials, screencasts, articles, and more.

    We care about the protection of your data. Read our Privacy Policy.

    logo Created with Sketch.

    Ruby on Rails tutorials, guides, and screencasts for web developers learning Ruby, Rails, Javascript, Turbolinks, Stimulus.js, Vue.js, and more. Icons by Icons8

    © 2020 GoRails, LLC. All rights reserved.