Oracle SQL Tutorial -4 Group Functions

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.