Hi ,
In this article , I will tell you about group functions .Group functions , when we do a query in the table if we write this query using group functions , the table presents us by dividing into specific groups .
GROUP FUNCTIONS
-
Group functions can be applied to the entire table as well as GROUP BY for subgroups .
SELECT group_function(column),.... FROM table [WHERE condition]
- AVG : Average value ,
- SUM : Total value ,
- MIN : Minimum value ,
- MAX : Maximum value ,
- COUNT : Number of records ,
- STDDEV : Standard deviation .
AVG AND SUM FUNCTIONS
SELECT AVG(CUST_CREDIT_LIMIT),SUM(CUST_CREDIT_LIMIT) FROM SH.CUSTOMERS;
MIN AND MAX FUNCTIONS
We use it to see the largest and smallest values . In the example below , we again questioned the largest and the smallest date of birth and our result is below .
-
Avg calculates the average value and sum total, in our example below we have seen the total and average credit limits of people .
SELECT MIN(CUST_YEAR_OF_BIRTH),MAX(CUST_YEAR_OF_BIRTH) FROM SH.CUSTOMERS;
Let’s make a similar example .
SELECT MIN(SALARY),MAX(SALARY) FROM ADMIN.NEW_TABLE;
In the query you see above, we want to see the lowest and highest salaries among the people in the table named new_table .
COUNT FUNCTION
-
Calculates the total number of values in a field . For example , in the example below , we wanted to find out how many people were in the city and how many different cities they were in , and we used the COUNT function .
SELECT COUNT(*),COUNT(DISTINCT CUST_CITY_ID) FROM SH.CUSTOMERS;
DISTINCT FUNCTION
-
It is used to group the data in columns or columns so that the result is displayed once .
SELECT DISTINCT CUST_CITY_ID FROM SH.CUSTOMERS;
GROUP BY EXPRESSION
-
Allows rows in a table to be divided into smaller groups .
-
Column aliases cannot be used in the Group by function .
-
If we want to list people’s average credit limits :
SELECT CUST_ID,AVG(CUST_CREDIT_LIMIT) FROM SH.CUSTOMERS GROUP BY CUST_ID ORDER BY 1;
HAVING EXPRESSION
-
When we use Where , we cannot restrict groups .
-
If we want to make a group restriction , we need to use HAVING .
-
In the example below , we’ve listed people with a credit limit greater than 7000 :
SELECT CUST_ID,AVG(CUST_CREDIT_LIMIT) FROM SH.CUSTOMERS GROUP BY CUST_ID HAVING AVG(CUST_CREDIT_LIMIT)>7000;
Let’s see the expression having a new example :
SELECT FIRST_NAME,SUM(SALARY) FROM ADMIN.NEW_TABLE WHERE FIRST_NAME NOT LIKE '%LIKE%' GROUP BY FIRST_NAME HAVING SUM(SALARY)>4000 ORDER BY SUM(SALARY);
In the query we wrote above “-like” we want to see people whose name is not mentioned and whose salary is greater than 4000 .
Nested-Group Functions
-
Highest credit average and person with :
SELECT MAX(AVG(CUST_CREDIT_LIMIT)) FROM SH.CUSTOMERS GROUP BY CUST_ID;
See you in my next post.