SQLite GROUP BY Clause

SQLite is a popular relational database that allows you to easily organize and query data. The GROUP BY clause is an essential construct for aggregating and summarizing data.

Let’s walk through GROUP BY using some concrete examples. We’ll start by creating a simple table called sales:

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  product TEXT, 
  unit_price INTEGER,
  units INTEGER  
);

INSERT INTO sales VALUES
  (1, 'T-shirt', 10, 5),
  (2, 'Hoodie', 15, 3),
  (3, 'T-shirt', 10, 2),
  (4, 'Hoodie', 15, 4),
  (5, 'T-shirt', 12, 3);

This table tracks product sales with the number of units sold and the per-unit price.

Also read: SQLite ORDER BY Clause

Grouping and Aggregating Data

The GROUP BY clause groups rows with the same value in the specified column(s). For each group, you can apply aggregate functions like SUM() and COUNT() to summarize the data.

For example, to get the total units sold for each product:

SELECT product, SUM(units)  
FROM sales
GROUP BY product;

This will return:

| product | SUM(units) |
|---------|------------|  
| T-shirt |         10 |
| Hoodie  |          7 |

We grouped all rows by product, then applied the SUM() aggregate to total the units sold.

You can group by multiple columns to get more granular summaries:

SELECT product, unit_price, SUM(units)
FROM sales
GROUP BY product, unit_price; 

Now we’ll see:

| product | unit_price | SUM(units) |
|---------|------------|------------|
| T-shirt |         10 |          5 |
| T-shirt |         12 |          3 |  
| Hoodie  |         15 |          7 |

Grouping by both product and unit_price shows us the units sold for each product at each price point.

Also read: SQLite GLOB Clause

Filtering Groups with HAVING

The HAVING clause adds filter conditions on groups. For instance, to only see products that sold more than five units:

SELECT product, SUM(units) 
FROM sales
GROUP BY product
HAVING SUM(units) > 5;

This will filter down to:

| product | SUM(units) |
|---------|------------|
| T-shirt |         10 |

HAVING works just like WHERE, but it operates on grouped rows rather than individual rows. This is useful for limiting aggregates based on certain criteria.

Ordering Grouped Data

You can order the grouped data using ORDER BY. For example:

SELECT product, SUM(units)
FROM sales 
GROUP BY product
ORDER BY SUM(units) DESC;

Will order with bestselling products first:

| product | SUM(units) |
|---------|------------| 
| T-shirt |         10 |
| Hoodie  |          7 |

Conclusion

This covers the core functionality of GROUP BY in SQLite. With some practice and real-world examples, you can summarize, analyze, and understand your data easily. GROUP BY is invaluable for aggregating data into meaningful groupings. Combine it with other clauses like ORDER BY and HAVING to unlock powerful analytical capabilities from your SQLite databases.