SQL GROUP BY Example

SQL GROUP BY Example

In this section we will discuss about the group by statement in SQL.

In this tutorial we will use MySQL for using the SQL statements. GROUP BY clause groups the same types of records of a table. This clause can be used with or without the SQL aggregate functions written with the SELECT......FROM...... or SELECT.....FROM....WHERE..... statement. This clause must be used after the FROM or WHERE.

Syntax :

To use the GROUP BY clause you can use either of the following syntax as per your requirements :

SQL GROUP BY clause with SELECT...FROM and WHERE statement

SELECT col1, col2,...coln, aggregate_function(col_name(onto which operation has to be performed))
FROM table_name
WHERE condition
GROUP BY col1,col2,...,coln

SQL GROUP BY clause with SELECT....FROM statement.

SELECT col1,col2,...,coln, aggregate_function() FROM table_name GROUP BY col1,clo2...coln

GROUP BY clause without aggregate function:

SELECT col1,col2,....,coln FROM table_name GROUP BY col1, col2,..., coln

NB : GROUP BY should be used after the FROM and WHERE clause.

Example

An example given below demonstrates that what the GROUP BY keyword exactly does. In this example I have created a table named order which contains the two columns item and quantity, the column item explains the item's name and the column quantity explains the number of items. Then I have inserted values into the respective columns and then write the SQL query of GROUP BY to display the sum of each item's quantity into the table i.e. Let the table 'order' contains the records as follows :

then the sum of above records should be as follows :

First I have created a table named order as follows :

CREATE TABLE `order` (                  
          `item` varchar(15) DEFAULT NULL,      
          `quantity` int(5) DEFAULT NULL       
        ) 

Then inserted values to the corresponding fields as follows :

insert into `order`(`item`,`quantity`) values ( 'tea','4')
insert into `order`(`item`,`quantity`) values ( 'teaCup','5')
insert into `order`(`item`,`quantity`) values ( 'tea','6')
insert into `order`(`item`,`quantity`) values ( 'teaCup','5')
insert into `order`(`item`,`quantity`) values ( 'spoon','5')

So, the table after inserting the record will be as follows :

Then to find the sum of each record write query as follows :

select item, sum(quantity) from `order` group by item

Now the records will be as follows :