Tag: google sheets query

The QUERY function in Google Sheets is one of the most powerful and versatile tools available. It allows you to perform SQL-like data manipulations—filtering, sorting, aggregating, and grouping—on your spreadsheet data.

---

📌 QUERY Function Syntax

QUERY(data, query, [headers])

🔹 Arguments:

1. data: The range of cells that you want to query.
2. query: A string written in a pseudo-SQL format.
3. headers (optional): The number of header rows at the top of your data (default is 1).

---

🧠 Why Use QUERY?

It combines the power of multiple functions like FILTER, SORT, VLOOKUP, SUMIF, UNIQUE, and even PIVOT TABLES—all in one.

---

✅ Basic Examples

Assume we have the following data in A1:D6:

Name	Age	Department	Salary
John	25	Sales	30000
Alice	30	HR	35000
Bob	24	Sales	28000
Carol	29	Marketing	40000
Dave	35	HR	38000

---

🔹 1. Select All Rows

=QUERY(A1:D6, "SELECT *", 1)

🔸 Returns the full table.

---

🔹 2. Select Specific Columns

=QUERY(A1:D6, "SELECT A, C", 1)

🔸 Returns only Name and Department columns.

---

🔹 3. Filtering Rows (WHERE Clause)

=QUERY(A1:D6, "SELECT A, D WHERE C = 'Sales'", 1)

🔸 Shows Name and Salary of employees in Sales department.

---

🔹 4. Using Comparison Operators

=QUERY(A1:D6, "SELECT A, B WHERE D > 30000", 1)

🔸 Returns Name and Age of employees earning more than 30,000.

---

🔹 5. Sorting (ORDER BY)

=QUERY(A1:D6, "SELECT A, D ORDER BY D DESC", 1)

🔸 Returns Name and Salary sorted by Salary in descending order.

---

🔹 6. Grouping and Aggregating (GROUP BY)

=QUERY(A1:D6, "SELECT C, AVG(D) GROUP BY C", 1)

🔸 Calculates average salary per department.

---

🔹 7. Labeling Columns

=QUERY(A1:D6, "SELECT C, AVG(D) GROUP BY C LABEL AVG(D) 'Average Salary'", 1)

🔸 Adds a custom label to the aggregated column.

---

🔹 8. Limit Results

=QUERY(A1:D6, "SELECT * LIMIT 3", 1)

🔸 Returns only the first 3 rows.

---

🔹 9. Combining WHERE and ORDER BY

=QUERY(A1:D6, "SELECT A, D WHERE D > 30000 ORDER BY D DESC", 1)

🔸 Filters employees with salary > 30,000 and sorts them in descending order.

---

🔹 10. Dynamic Query with Cell Reference

=QUERY(A1:D6, "SELECT A, D WHERE D > "&E1, 1)

🔸 Assuming cell E1 has the value 30000, this will filter dynamically.

---

⚠️ Notes:

• Text values in queries must be enclosed in single quotes (‘ ‘).
• Numbers and cell references can be added directly.
• QUERY is case-insensitive by default.

---

🎯 Common Use Cases

• Creating dashboards
• Creating dynamic reports
• Filtering datasets based on dropdown selections
• Summarizing large data tables
• Converting flat data into summarized views like pivot tables

---

📘 Real-Life Example:

Imagine a school with student records:

Student	Class	Subject	Marks
Rahul	10	Math	85
Sneha	10	Science	90
Aman	11	Math	78
Priya	10	Math	92

To find average marks in each subject for class 10:

=QUERY(A1:D5, "SELECT C, AVG(D) WHERE B = 10 GROUP BY C", 1)

🔸 Returns Math and Science with their average marks for class 10 students.

---

💼 Top 10 Interview Questions on Google Sheets QUERY Function

1. Q: What is the QUERY function in Google Sheets?
   A: It allows you to use SQL-like queries to filter, sort, group, and summarize data.
2. Q: How do you filter records using a text value in QUERY?
   A: Use WHERE column = 'Text', e.g., WHERE C = 'Sales'.
3. Q: How can you sort data using QUERY?
   A: Use ORDER BY clause: ORDER BY column [ASC|DESC].
4. Q: What does GROUP BY do in QUERY?
   A: It aggregates values (e.g., SUM, AVG) based on unique groups in a column.
5. Q: How do you rename column headers in the QUERY result?
   A: Use the LABEL clause: LABEL AVG(D) 'Average Salary'.
6. Q: What’s the difference between SELECT * and SELECT A, B?
   A: SELECT * selects all columns; A, B selects only specific columns.
7. Q: How can you use a cell reference in a QUERY?
   A: Concatenate it: "SELECT A WHERE B > "&E1
8. Q: Can you use OR and AND in QUERY filters?
   A: Yes. Example: WHERE B > 25 AND C = 'HR'
9. Q: What happens if you omit the headers parameter?
   A: QUERY assumes the first row is the header by default (1).
10. Q: How is QUERY different from FILTER function?
    A: FILTER is simpler and only filters data. QUERY is more powerful with sorting, aggregation, grouping, and SQL-like operations.

---