Aggregate functions on windows azure storage tables

Question

I have a Windows Azure Storage table in which i am storing more than 1000k+ rows(entities). I want to perform a few aggregate functions on the table, for example counting on a specific column for a specific condition, average, total, min, max and group by (as in normal SQL). How can I do that in windows Azure Storage tables?

Answer 1

The only way is pretty much to pull down all your entities and run your aggregates on them in memory.

If you have to keep this information up to date I would store these aggregates in another place and update them every time a new entity is added. For instance the average is the sum/count so you can have a table item that stores the current sum and count and when you add a new entity also update the sum and count values.

Make sure to use optimistic concurrency so you can catch any race conditions etc. If your table entities share the same partition key then you can even do the operation on the same transaction.