Writing Your First Real SQL Query: From SELECT to GROUP BY

If you read the first post in this series, you already have the foundation: tables store structured records, rows represent individual facts, and keys connect related data. Now we can move to the next step and start writing queries that actually answer questions.

A lot of beginners can follow SQL when someone else writes it, but freeze when the editor is empty. They know the words SELECT and WHERE, but they do not yet have a reliable way to think through a query from scratch. That is what this post is for.

We will keep the same small ecommerce dataset from the previous article. The goal is not to memorize clauses in isolation. The goal is to learn how those clauses map to the questions you are asking the database.

Why can you read SQL but still struggle to write it?

Because reading a finished query is like reading a solved example. Writing your own query is a different skill. It requires turning a vague question into a sequence of decisions.

Suppose you want to answer, "Which orders belong to customer 1?" Hidden inside that simple sentence are several choices:

1. Which columns do I want to see?
2. Which rows do I want to keep?
3. How should the result be ordered?

Now change the question to, "How many orders does each customer have?" That is no longer just about showing rows. It is about summarizing them. That means your thinking process changes, and GROUP BY becomes necessary.

The important idea is that SQL clauses are not random building blocks. Each one represents part of a reasoning process.

Think of a query as a question to a table

Here is a useful mental model:

• SELECT asks: what do I want to see?
• FROM asks: where am I getting it from?
• WHERE asks: which rows should be filtered out?
• ORDER BY asks: how should the result be arranged?
• GROUP BY asks: at what level should repeated rows be summarized?

When you think this way, SQL becomes much easier to compose. Consider these two questions:

• Question A: show all orders.
• Question B: count how many orders each customer placed.

Question A is about listing records. Question B is about aggregating them. That difference is exactly why Question B needs GROUP BY and Question A does not.

Learn the four most common clauses first

Start with the most direct query possible:

SELECT order_id, customer_id, order_date
FROM b2_orders;

This simply returns three columns from the orders table. No filtering. No sorting. It is the equivalent of opening the table and looking at the full set of records.

Now suppose you only want to see orders for one customer. Add a WHERE clause:

SELECT order_id, customer_id, order_date
FROM b2_orders
WHERE customer_id = 1;

That tells the database to keep only the rows where customer_id equals 1.

If you want the most recent orders first, add ORDER BY:

SELECT order_id, customer_id, order_date
FROM b2_orders
WHERE customer_id = 1
ORDER BY order_date DESC;

At this point, you can already answer useful questions like, "When did this customer order most recently?"

But if your question changes to, "How many orders does each customer have?" you are no longer listing one order per row. You are summarizing rows by customer. That is where GROUP BY comes in:

SELECT customer_id, COUNT(*) AS total_orders
FROM b2_orders
GROUP BY customer_id
ORDER BY total_orders DESC;

You can think of GROUP BY as, "Put matching rows into buckets, then calculate something for each bucket." In this case, the buckets are customers and the calculation is COUNT(*).

Run the query progression in the Playground

The interactive snippet below uses the same ecommerce story as the first post, but with table names dedicated to this article. Start with the default query, then turn it into the filtered, sorted, and grouped versions shown above. The best way to practice is to change one small piece at a time and watch the result change with it.

Try these next:

SELECT order_id, customer_id, order_date
FROM b2_orders
WHERE customer_id = 1
ORDER BY order_date DESC;

SELECT customer_id, COUNT(*) AS total_orders
FROM b2_orders
GROUP BY customer_id
ORDER BY total_orders DESC;

SELECT city
FROM b2_customers
ORDER BY city ASC;

As you practice, keep asking yourself one question: am I listing records, or am I summarizing them? That single distinction helps you decide whether GROUP BY belongs in the query.

Here is the same idea as an animation. Watch five order rows collapse into three customer groups: the rows stay detailed on the left, then the grouped result appears on the right.

What is the real difference between listing rows and summarizing them?

A lot of beginners treat GROUP BY like a more advanced form of sorting. It is not. Sorting changes the order of rows you already have. Grouping changes the grain of the result.

An orders table normally has one row per order. After you group by customer, the result no longer has one row per order. It has one row per customer. That is a different level of detail.

This also explains why grouped queries cannot freely include random columns in SELECT. If one group contains many rows, the database needs to know which value you want to show for that group. Once you understand that grouping changes the grain of the result, many common SQL errors start to make sense.

A simple order for thinking through queries

A useful way to think is:

1. First decide what the result should look like.
2. Then decide which table or tables it comes from.
3. Then decide whether rows need to be filtered.
4. Then decide whether the answer needs a summary.
5. Finally decide how the result should be ordered.

Your typing order in SQL is not always the same as your thinking order, but your mental process matters. Many beginners jump straight into GROUP BY or ORDER BY before they have decided what they are even trying to show.

Wrap-up: writing queries is really about breaking questions into steps

The point of this post is not just to define four clauses. It is to help you build a rhythm for writing SQL: what do I want, where does it come from, what should be filtered, does it need a summary, and how should it be ordered?

Once that rhythm becomes familiar, an empty editor stops feeling so intimidating.

Related Articles

• Understanding SQL Tables, Rows, Columns, and Keys for the table and relationship model that makes FROM and joins feel concrete.
• From SQL Queries to Analysis: Answering Real Questions with Data for the next step after basic clauses, where you turn grouped queries into actual business analysis.
• SQL ORDER BY and LIMIT: Sorting and Restricting Results for a deeper look at result ordering once basic query construction feels comfortable.

Next, we will use the same dataset to move from querying into analysis. Instead of just listing or counting rows, we will answer business questions like which category sells best and how sales change over time. Continue with From SQL Queries to Analysis: Answering Real Questions with Data.

If you want to review the data model first, go back to Understanding SQL Tables, Rows, Columns, and Keys.