Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

How to create dynamic aggregate query?

Topic Labels: Formulas
2103 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Henry_Farkas
4 - Data Explorer
4 - Data Explorer

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

2 Replies 2

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
image

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
image

Now that we have a lookup getting all of the cash amounts we add a Roll Up field to sum them
image

image

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.

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