Query to extract unique values from 1 column and add matching numeric values

Question

I have multiple sets of 2 columns (COL_LEFT and COL_RIGHT) and would like to extract all the unique values (except the blanks) from COL_LEFT and display matching values from COL_RIGHT alphabetized by COL_LEFT values. If values in COL_LEFT are duplicates, I would like the final COL_RIGHT to show the sum of all values.

My query formula fails, please see the example with the problem and desired output: https://docs.google.com/spreadsheets/d/1-aXqlYmamPI7QKad4qxacVRrC9Q6uKdDdNDUjJOvrak/edit?usp=sharing

Thank you

Answer 1

Instead of GROUP BY Col1, Col2, you should instead only use GROUP BY Col1. Read the note for the GROUP BY clause here.

Note: If you use a group by clause, then every column listed in the select clause must either be listed in the group by clause, or be wrapped by an aggregation function.

One usually doesn't need to include an aggregated column (that is a column within a SUM() or some other) in the GROUP BY clause. There is probably an edge case for that, but for the most conventional use, put the column with the non-numeric values in the GROUP BY clause, and exclude the aggregated column.