Level 3QUERYReading, 6 min
QUERY: SQL inside a cell
QUERY asks a table a question in a small language borrowed from SQL. One formula can filter, group, sum, sort and rename:
=QUERY(A1:E13, "select B, sum(E) where C > 1000 group by B order by sum(E) desc limit 3")
Columns are named by their letters. The clauses always come in this order, and you can skip any of them:
| Clause | Does | Like |
|---|---|---|
select | picks columns and totals | choosing columns |
where | keeps matching rows | FILTER |
group by | one row per value | a pivot table |
order by | sorts, desc for biggest first | SORT |
limit | keeps the first N rows | top N |
label | renames result headers | typing a header |
Totals are sum, avg, count, min and max. Any selected column that isn't inside one must appear in group by.
Quotes trip everyone up. The whole query goes in double quotes. Text inside it goes in single quotes: where B = 'Paid Search'. Conditions join with and and or, and contains matches part of a text value.
Point QUERY at a range that includes the header row. It uses the headers to label results and drops them from the math.