Web | This Site

Learning Basic SQL
Advanced SQL
SQL Functions
SQL Useful Resources
Selected Reading
© 2011 TutorialsPoint.COM

| Home |  | References |  | Discussion Forums |  | About TP

# SQL - Group By

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

# Syntax:

The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

    SELECT column1, column2
    FROM table_name
    WHERE [ conditions ]
    GROUP BY column1, column2
    ORDER BY column1, column2

# Example:

Consider CUSTOMERS table is having following records:

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
    |  2 | Khilan   |  25 | Delhi     |  1500.00 |
    |  3 | kaushik  |  23 | Kota      |  2000.00 |
    |  4 | Chaitali |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal    |  8500.00 |
    |  6 | Komal    |  22 | MP        |  4500.00 |
    |  7 | Muffy    |  24 | Indore    | 10000.00 |
    +----+----------+-----+-----------+----------+

If you want to know the total amount of salary on each customer, then GROUP BY query would be as follows:

    SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
         GROUP BY NAME;

This would produce following result:

    +----------+-------------+
    | NAME     | SUM(SALARY) |
    +----------+-------------+
    | Chaitali |     6500.00 |
    | Hardik   |     8500.00 |
    | kaushik  |     2000.00 |
    | Khilan   |     1500.00 |
    | Komal    |     4500.00 |
    | Muffy    |    10000.00 |
    | Ramesh   |     2000.00 |
    +----------+-------------+

Using above example you would not have a good explaination. So now let us has following table where CUSTOMERS table has following records:

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
    |  2 | Ramesh   |  25 | Delhi     |  1500.00 |
    |  3 | kaushik  |  23 | Kota      |  2000.00 |
    |  4 | kaushik  |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal    |  8500.00 |
    |  6 | Komal    |  22 | MP        |  4500.00 |
    |  7 | Muffy    |  24 | Indore    | 10000.00 |
    +----+----------+-----+-----------+----------+

Now again, if you want to know the total amount of salary on each customer, then GROUP BY query would be as follows:

    SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
         GROUP BY NAME;

This would produce following result:

    +---------+-------------+
    | NAME    | SUM(SALARY) |
    +---------+-------------+
    | Hardik  |     8500.00 |
    | kaushik |     8500.00 |
    | Komal   |     4500.00 |
    | Muffy   |    10000.00 |
    | Ramesh  |     3500.00 |
    +---------+-------------+

Advertisement

Online Image Processing

Indian Baby Names