sqlite
  1. sqlite-group-by
PreviousNext

SQLite Clauses/Conditions: Group By

In SQLite, the Group By clause is used to group the result set returned by a SELECT statement based on one or more columns. This is useful for calculating aggregate functions on subsets of data and creating summary reports.

Syntax

The syntax for the Group By clause is as follows:

SELECT column1, [column2, ...]
FROM table
GROUP BY column1, [column2, ...];

Example

Suppose we have a table called sales with columns product, quantity, and price. We can use the Group By clause to calculate the total sales for each product.

SELECT product, SUM(quantity * price) as total_sales
FROM sales
GROUP BY product;

Output

The output of the above example would be:

product | total_sales
--------|------------
apples  | 150.00
oranges | 100.00
bananas | 20.00

Explanation

In the above example, we use the Group By clause to group the sales table by product. We then use the SUM function to calculate the total sales for each product by multiplying the quantity and price columns. Finally, we use the as keyword to give an alias to the total sales column.

Use

The Group By clause is useful for creating summary reports and for calculating aggregate functions on subsets of data. It is commonly used in financial and sales reporting, where you may want to view total sales for each product or each salesperson.

Important Points

  • The Group By clause can only be used in conjunction with a SELECT statement.
  • All columns in the SELECT statement that are not part of an aggregate function must be included in the Group By clause.
  • The order of the columns in the Group By clause affects the ordering of the result set.
  • You can use multiple columns in the Group By clause to group the data by multiple criteria.

Summary

In this tutorial, we learned about the Group By clause in SQLite and how it is used to group the result set of a SELECT statement based on one or more columns. We saw an example of using the Group By clause to calculate total sales for each product in a sales table. It's important to include all non-aggregate columns in the Group By clause and be aware of the ordering of the columns in the result set.

Order ByHaving
Published on: