I have this table:

|    A    |  B  |
| grocery | $10 |
| clothes | $40 |
| grocery | $19 |


So I'd like to have a column with an aggregation of the categories like grocery and clothes with the total expenses, what in SQL would be a JOIN with the aggregate function SUM()

How can I achieve this?


This is what I think you're looking for:


=QUERY(DATA!B1:C3;"SELECT B, SUM(C) GROUP BY B label B 'Type', SUM(C) 'Sum'")


Column C is aggregated by column B through a summation. Labels are added. In this particular case, the usage of JOIN in an SQL statement isn't applicable, since the aggregation is done on one table.





I've created an example file for you: Sum Aggregate

| improve this answer | |
  • 1
    I changed the label from 'Type' to 'Thank you very much sir! it worked like a charm! :D' – Petruza Oct 30 '13 at 21:53

There is a general Pivot table tool included in the Spreadsheet application. You find it under Data → Pivot table report.

This tool is typically good for _give me the sum of B (price) partitioned per A (category)"

This would be a pivot table with row of A and value of B, show sum() which can be built using the pivot table tool.

| improve this answer | |

I solved this by using JavaScript LINQ (Language Integrated Query).

=linq("Enumerable.from('Orders').take(100).join('People', '$.Region', '$.Region',(l, r)=> {return {...l,...r}})", State!$A$1)

It lets you specify JavaScript with complex join conditions. You can also perform other SQL queries such as Grouping, Projecting, Sorting and Filtering your sheets as if they were database tables. Look at the links below.

Note that in the LINQ query language I replaced all spaces in column names with underscores to make them valid JS identifiers.



| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.