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
Group functions are used to produce aggregated result. Some of group functions are listed below.
MIN - Returns the minimum value from the given input.
MAX - Returns the maximum value from the given input.
COUNT - Returns the no of rows from the given input.
SUM - Returns the sum from the given input.
AVG - Returns the average value from the given input.
If you want to find out the total number of records in table the below query is used.
Select count(*) from mobile_sales_details count(*) ---------- 67
Here * denotes all row Sum is used to get sum of all the mobiles price
Select sum(price) from mobile_sales_details sum(price) ---------- 996819
Min is used to find out the minimum value from given input. Here we are going to find out lowest mobile cost. 882 is the lowest price of mobile listed in the table
Select min(price) from mobile_sales_details min(price) ---------- 882
If you want to find out the maximum valued mobile use max group function. 62,500 is the maximum mobile price in the given table.
Select max(price) from mobile_sales_details max(price) ---------- 62500
Avg is used to find out the average of the given input. Here we are going to find out the average price of mobile phone. 14,877 is the average price from 67 mobiles price.
Select avg(price) from mobile_sales_details avg(price) ---------------- 14877.8955223881
Nulls column values:
Group functions always skip null when they are used in the column. In the previous example we used count(*). If all column in a row is null, the count function will skip the row as to count.
Here we are using discount column for count operation. The discount column has more null values.
So the count function will skip the null and returns non null rows as their count.
Select count(discount) from mobile_sales_details (in source file make the discount field empty as null then only it will work) count(discount) --------------- 29
See the above result. It returns 29 as their count. We have 67 rows but only 29 rows of discount column has values. Likewise sum, avg, min, max will not consider null.
Especially while using avg it will affect the result. If you applying avg function to null values column you won’t get the exact result. Note this.
While using group functions we cannot take normal columns in the select list. If you using group function in retrieval list, We must choose another also be a group function applied column.
See the below example works fine.
Select sum(price), min(price), max(price) from mobile_sales_details sum(price) min(price) max(price) ---------- ---------- ---------- 996819 882 62500
See the below example. Here we chose normal column in select list
Select sum(price),price from mobile_sales_details sum(price) price ---------- ---------- 996819 43490
It will show the last price column value in the table. However, other rdbms will throw an error for these queries. Because when getting result of sum of price we cannot choose any other column, but SQLite allow this. Logically it is a mistake while getting a sum of value we cannot choose normal column, because each column value associated with another column value, but sum of price is single value, we cannot mix it with price. Furthermore, at the same time we cannot use group functions in where statement.
However, if you need to compare the group functions result we have one alternative solution, i.e., "having" keyword. "Having" keyword acts as like where function, but it only works for group functions. If you working on group function go for having keyword or else use the normal where keyword.
The below query will throw an error
Select * from mobile_sales_details where price=sum(price)
The below query is used to get the Samsung mobiles maximum price, minimum price and sum of those mobiles price.
Samsung’s low cost mobile is available at 1299 and it’s selling 54,300 as its maximum price.
Select sum(price),min(price),max(price) from mobile_sales_details where brand_name=’Samsung’ sum(price) min(price) max(price) ---------- ---------- ---------- 256522 1299 54300