``````Name | Value
A    | 10
B    | 100
A    | 20
B    | 200
C    | 1000
``````

How can I create another table based on a query:

``````select name, sum(value), count(value) from table group by name
``````

so it looks like this:

``````Name | SUM   | COUNT
A    | 30    | 2
B    | 300   | 2
C    | 1000  | 1
``````

By using the `QUERY` function you can achieve your goal.

## Formula

``````=QUERY(Data!A1:B6;"select A, sum(B), count(B) group by A")
``````

## Screenshot

Using other functions, makes it achievable as well.

``````=UNIQUE(Data!A2:A)
``````

add the following formulas in B2 and C2 and copy down to B4 and C4 respectively

``````=SUM(FILTER(Data!\$B\$2:B;Data!\$A\$2:A=A2))
=COUNTA(FILTER(Data!\$B\$2:B;Data!\$A\$2:A=A2))
``````

## Example

I've made an example file for you: QUERY vs. FUNCTIONS

• Google sheets docs on query Apr 24, 2017 at 2:13
• Thank you! I was looking for info on pivot tables (which I should learn more about), but the QUERY function is amazing. I never even knew it existed in Google Sheets. Jun 16, 2020 at 1:33
• Extra points for including an example spreadsheet! Kudos. Apr 19, 2021 at 15:13

• Pivot Tables should probably be mentioned in the question comments section instead. It is definitely helpful and related, but not answer the question precisely - only because the question was specifically about a query and the `QUERY` function is the answer.
• You should at least describe how a pivot table can be configured to solve this problem. Normally I `SELECT` data out of an SQL database that already has `GROUP BY`, so the concept of doing this with a GUI is very foreign. I'd like to learn how a pivot table can solve this problem, but without some explanation, this answer is useless to me. Aug 23, 2018 at 17:47