Open In Colab

< Previous Page | Home Page | Next Page >

Open in Colab

GROUP BY Clause

The GROUP BY clause is an optional clause of the SELECT statement. This clause groups a selected group of rows into summary rows by values of one or more columns.

The GROUP BY clause returns one row for each group. For each group, you can apply an aggregate function such as MIN, MAX, SUM, COUNT, or AVG to provide more information about each group. An overview of the aggregate functions can be found here

The following statement illustrates the syntax of the SQLite GROUP BY clause.


SELECT
    column_1,
    aggregate_function(column_2) 
FROM 
    table
GROUP BY 
    column_1,
    column_2;


The GROUP BY clause comes after the FROM clause of the SELECT statement. In case a statement contains a WHERE clause, the GROUP BY clause must come after the WHERE clause.

Following the GROUP BY clause is a column or a list of comma-separated columns used to specify the group.


Let's apply this clause to our STUDENT Table from above.


ROLL_NO NAME AGE BRANCH CITY
10001 Kate 22 CSE Mumbai
10002 Richard 21 ECE Delhi
10003 Rick 33 ME Chennai
10004 Peter 26 CSE Delhi
10005 Peter 22 CSE Noida
10006 Mark 22 ECE Jaipur
10007 Brad 23 ME Rampur


Let's say we want to group all students according to the branches and want to count the number of students within branches. The query for this would look as follows:

SELECT 
  BRANCH, 
  COUNT(BRANCH) as "Number of Students in Branch" 
FROM 
  STUDENT 
GROUP BY 
  BRANCH;

This results in the following output:

Branch Number of Students in Branch
CSE 3
ECE 2
ME 2


< Previous Page | Home Page | Next Page >

Open in Colab