Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 02, 2022 07:03 AM
Dear Community!
I’m fairly new to airtable, so please bear with me :slightly_smiling_face:
I’m trying to pull some aggregated data into a new table. Receipts are tracked into a table “Expenses” with a field for the amount, a field for the name of the person who made the expense and a field for the payment method (cash, bank transfer, paypal, etc.) - amongst other fields not related to my problem. Now I would like to get the sums of amounts aggregated by person and payment method.
With SQL I’d run “SELECT person, method, SUM(amount) FROM expenses GROUP BY person, method”
Ideally the table with the aggregated data is updated dynamically when rows are appended to the expenses table.
I’m aware of the possibility to create a view that does exactly what the SQL query does. However, I need to append the queried data with additional fields.
Any help on this is greatly appreciated.
Best regards
Henry
Jun 02, 2022 01:30 PM
Hi @Henry_Farkas
I think I can help you.
First, you have a table of expenses. You should also have a table of people. Your expenses table should link to people
In the people table, lets add a few fields: Airtable automatically created a link back to Expenses. So lets add a look up field from Expenses, Amount, and a condition that the Method is Cash
Now that we have a lookup getting all of the cash amounts we add a Roll Up field to sum them
Repeat the last few steps for each Method. Once you have each Sum by Method, you can create another formula to Sum each method for a total per person.
Jun 03, 2022 05:36 AM
@Vivid-Squid You don’t need to create any lookup fields before creating rollup fields. You can simply create rollup fields all on their own.