How to create dynamic aggregate query?

[Henry_Farkas]

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

[Vivid-Squid]

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.

[ScottWorld]

@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.