Google Sheets – How to Perform SQL JOIN and SUM Equivalent

google sheets

I have this table:

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

etc.

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?

Best Answer

This is what I think you're looking for:

Formula

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

Explained

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.

Screenshots

Data:
data

Result
result

Example

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

Related Solutions

Google Sheets – Sum on Condition

You can use the SUMIF function to calculate the total sum for each person.

If you enter the following formula in a cell you like, it will give you the total amount spent by "Me":

=SUMIF(A2:A6,"=Me",B2:B6)

where A2:A6 is the Range, "=Me" is the Criteria, and B2:B6 is the Sum_Range.

Google-sheets – Sorting unique rows by a related SUMIF column

I have had a similar issue, with trying to sort a UNIQUE dataset.

the way i got around it was by creating another sheet and using the '=QUERY()' function to call the sheet with the UNIQUE list on it. This way you can add a ORDER BY statement to the query.

so in your example, Expenses has the source data, the summary table has the Summary data (with the UNIQUE function),

and then add a new sheet called "Summary Sorted" and then add the query function like this:

=query(Summary!A1:B4, "SELECT A,B ORDER BY B DESC")

Here is an example spreadsheet to demonstrate: