General Note: Here we have pulled in the data from a sample database. You can download it from the following link and use it for practicing Click Here For Sample Database

In this tutorial, you will learn the use of  GROUP BY keyword in SQLite.

Group by is a reserved keyword, used to group the result based on the similar data.

Say for example, take a class. From the class marks list we can easily find out who is topper of the class. Generally a class has boys and girls. Sometimes you need to find out topper from boys and girls separately. If you use max function you can easily get topper from marks. However, topper from boys and girls cannot be retrieved by using max key word only. Here we need group by keyword.

If you group them based on their gender, then apply max function, you will get two top marks from the list. One is from the boys and another one is from girls. The below query is used to get the sum of price, minimum of price and maximum of price of particular brand. So the query is grouped by brand name.

Therefore, the result will produce sum, min and max of price from particular brand


Select sum(price),min(price),max(price) from mobile_sales_details group by  brand_name

sum(price)  min(price)  max(price)
----------  ----------  ----------
16999       16999       16999     
221811      21712       62500     
45996       5999        16999     
102595      9125        43490     
26295       2699        11999     
40456       1210        12749     
46950       3490        15990     
35385       882         9074      
44997       6999        23999     
74694       1249        17190     
256522      1299        54300     
84119       6150        28999

There is not much difference between the below and above query. In the below query I just added count in select list. So it will produce mobile phones count under brands


Select sum(price),min(price),max(price),brand_name,count(*) from mobile_sales_details group by  brand_name

sum(price)  min(price)  max(price)  brand_name  count(*)  
----------  ----------  ----------  ----------  ----------
16999       16999       16999       Alcatel     1         
221811      21712       62500       Apple       5         
45996       5999        16999       Asus        4         
102595      9125        43490       BlackBerry  5         
26295       2699        11999       Karbonn     5         
40456       1210        12749       Lava        5         
46950       3490        15990       Lenovo      5         
35385       882         9074        Micromax    5         
44997       6999        23999       Motorola    3         
74694       1249        17190       Nokia       8         
256522      1299        54300       Samsung     16        
84119       6150        28999       Sony        5

Up to now we used single column in group by keyword. Here we going to use more than one column in group by.

Here I used brand name followed by OS name. So first all brand data grouped then particular brand OS name to be grouped and we getting the output.


Select brand_name,os_name,count(*) from mobile_sales_details group by brand_name,os_name

brand_name  os_name                    count(*)  
----------  -------------------------  ----------
Alcatel     Android v4.2 (Jelly Bean)  1         
Apple       iOS 6                      2         
Apple       iOS 7                      3         
Asus        Android v4.3 OS            4         
BlackBerry  BlackBerry 10 OS           2         
BlackBerry  BlackBerry 10.2.1 OS       1         
BlackBerry  BlackBerry 7.1 OS          1         
BlackBerry  BlackBerry OS              1         
Karbonn     Android v4.2 (Jelly Bean)  3         
Karbonn     Android v4.4 (KitKat) OS   2         
Lava                                   1         
Lava        Android v4.2 (Jelly Bean)  1         
Lava        Android v4.3 OS            1         
Lava        Android v4.4 (KitKat) OS   2         

In the above query it displayed result as brand name and their OS name with available OS count.

The below query used to display brand name and their mobiles count. Here apple has 5 phone and Sony also has 5, etc,.


Select count(*) ,brand_name from mobile_sales_details group by brand_name

count(*)    brand_name
----------  ----------
1           Alcatel   
5           Apple     
4           Asus      
5           BlackBerry
5           Karbonn   
5           Lava      
5           Lenovo    
5           Micromax  
3           Motorola  
8           Nokia     
16          Samsung   
5           Sony

 


Select max(count(*)) from mobile_sales_details group by brand_name

Cannot do the nesting of aggregate functions. In the above query I tried find out which brand which is having maximum no of mobile phones.

However, it is not working in SQLite, but nesting of group function will work on other rdbms.