SQL GROUP BY

Gaurav Shegekar from ByteCraftOct 22, 2026

SQL GROUP BY

Turn Thousands of Rows into Useful Insights

Oct 22

Imagine you’re building an e-commerce application. Your manager asks a simple question: “How much revenue did we generate from each product category last month?”

Your database contains thousands of orders. You could fetch every order, loop through the records in Java, group them by category, and calculate the total. But why do all that manually when SQL can do the heavy lifting for you?

That’s exactly where the GROUP BY clause comes in. It helps you organize rows with common values into groups and calculate useful summaries for each group. Let’s understand how it works using practical examples you’ll recognize as a backend developer.

What Is GROUP BY in SQL?

The GROUP BY clause combines rows that share the same value in one or more columns. You can then use aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to calculate a result for each group. Instead of looking at every individual record, you get a summary that’s easier to understand and use in reports, dashboards, and APIs.

Suppose we have an orders table like this:

order_idcustomer_idcategoryamount1011Electronics150001022Clothing20001031Electronics80001043Clothing30001052Books10001064Electronics12000

There are six orders across three categories. If we want to know how much revenue each category generated, listing all six orders doesn’t directly answer the question. We need to group the orders by category and calculate the total amount for each group.

1. Your First GROUP BY Query

Let’s start with the simplest example. We want to see each category only once, even though several orders may belong to the same category.

SELECT category
FROM orders
GROUP BY category;

The result contains three rows: Electronics, Clothing, and Books. SQL puts rows with the same category value into the same group. Since we selected only the category, we get one result for each distinct category represented in the data.

However, grouping becomes much more useful when you combine it with aggregate functions. That’s when you can answer questions such as how many orders belong to each category or how much revenue each category generated.

2. Calculate Totals Using SUM()

Imagine your manager wants the total revenue generated by each category. The SUM() function adds values together, while GROUP BY determines which rows should be included in each total.

SELECT
    category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY category;

The result looks like this:

categorytotal_revenueElectronics35000Clothing5000Books1000

Notice what happened. SQL added the amounts of all Electronics orders together, did the same for Clothing, and calculated a separate total for Books. One query has turned six individual records into three useful summaries. This is the kind of operation that makes GROUP BY valuable for sales reports, analytics dashboards, and financial summaries.

3. Count Records Using COUNT()

Now suppose your application needs to display how many orders each category has received. You don’t need to retrieve every order and maintain counters in Java. You can use COUNT() with GROUP BY to calculate the number of rows in each category.

SELECT
    category,
    COUNT(*) AS total_orders
FROM orders
GROUP BY category;

The result shows three Electronics orders, two Clothing orders, and one Books order. Here, COUNT(*) counts the rows in each group, including rows where some individual column values might be NULL. That’s different from COUNT(column_name), which counts only rows where that particular column isn’t NULL.

This distinction can matter when your tables contain optional fields. For example, counting all orders and counting only orders with a recorded delivery date can produce different results. Choosing the right aggregate function helps ensure that your report answers the question you actually intended to ask.

4. Other Useful Aggregate Functions

Besides SUM() and COUNT(), SQL provides other aggregate functions that work naturally with GROUP BY. AVG() calculates the average, MAX() finds the largest value, and MIN() finds the smallest value within each group. These functions help answer different business questions without requiring you to process each record individually in application code.

For example, to calculate the average order amount for each category, you can write:

SELECT
    category,
    AVG(amount) AS average_order_amount,
    MAX(amount) AS largest_order,
    MIN(amount) AS smallest_order
FROM orders
GROUP BY category;

The database calculates these values separately for Electronics, Clothing, and Books. One query can therefore provide several useful metrics for each category. Remember that aggregate functions generally ignore NULL values in the column they process, so your results depend on the quality and completeness of your data.

5. Group by Multiple Columns

Sometimes grouping by one column isn’t enough. Imagine you want to understand how much each customer has spent in each product category. Grouping only by customer_id would combine all categories purchased by that customer, while grouping only by category would combine purchases from every customer.

You can group by both columns:

SELECT
    customer_id,
    category,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id, category;

Now SQL creates a separate group for each unique combination of customer and category. Customer 1 has spent ₹23,000 on Electronics, while customer 2 has spent ₹2,000 on Clothing and ₹1,000 on Books. This approach is useful for customer spending reports, monthly sales breakdowns, and other situations where you need to analyze data across multiple dimensions.

6. WHERE vs. HAVING: A Common Confusion

Here’s where many beginners get confused. The WHERE clause filters individual rows before SQL groups them, while HAVING filters the groups after aggregate calculations have been performed. They may look similar, but they serve different purposes. Understanding the difference is essential when building reports with conditions on totals or counts.

Suppose you want to display only categories that generated more than ₹5,000 in revenue.

SELECT
    category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 5000;

This returns Electronics and Clothing, but not Books. We use HAVING because the condition depends on the total calculated for each group. If we wanted to exclude individual orders below ₹2,000 before calculating category revenue, we’d use WHERE amount >= 2000 before GROUP BY instead. In short, filter rows with WHERE and filter aggregated groups with HAVING.

7. A Practical Query for Backend Developers

Let’s combine these concepts into a realistic reporting query. Imagine you’re building an admin dashboard that should display categories with more than one order, sorted by their total revenue from highest to lowest.

SELECT
    category,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING COUNT(*) > 1
ORDER BY total_revenue DESC;

The query groups orders by category, counts the orders, calculates total revenue, removes categories with only one order, and sorts the remaining results by revenue. The dashboard can now display a useful summary without receiving every individual order record. In a Spring Boot application, you could use a query like this in a repository or reporting service, depending on your data-access approach.

One important detail: when using GROUP BY, every selected column generally needs to be grouped or used inside an aggregate function, unless the database can establish a valid functional dependency. Otherwise, SQL cannot determine which individual value to return for a group containing multiple different values.

The Takeaway

GROUP BY helps you turn raw database records into meaningful summaries. Combine it with SUM(), COUNT(), and other aggregate functions to calculate totals, count records, and understand patterns in your data. Add HAVING when you need to filter groups based on those calculated results, and use WHERE when you need to filter individual rows before grouping.

For backend developers, this isn’t just an interview topic. It’s useful when building admin dashboards, sales reports, customer analytics, and APIs that return summarized data. Knowing how to write the query also helps you decide when the database should perform the aggregation instead of pulling unnecessary records into your Java application.

Now here’s a question: If you’re building an e-commerce dashboard, would you calculate category-wise revenue using SQL GROUP BY or fetch all orders and calculate the totals in Java? Why?

Share your reasoning in the comments. I’d love to hear how other developers approach this problem.

Follow ByteCraft for practical SQL, Java, and backend development lessons.

Thanks for reading ByteCraft! Subscribe for free to receive new posts and support my work.

Pledge your support

ByteCraft is free today. But if you enjoyed this post, you can tell ByteCraft that their writing is valuable by pledging a future subscription. You won't be charged unless they enable payments.

Pledge your support