AGGREGATE FUNCTION OR GROUP FUNCTION IN SQL
SQL categories function in two set one in single row function and another is aggregate or group function.
Aggregate Function :
SQL provides several sets ,or aggregate function or group function such as
consider the table EMP
EMPNO ENAME DETPNO SALARY
101 amit 10 40000
102 sumit 10 25000
103 jatin 20 30000
104 lalit 20 25000
105 smita 30000
1) SUM()
The sum function return sum of all the values of the selected column.
Syntax:
SELECT SUM(col_name)
FROM table_name
WHERE condition;
Ex:
a)SELECT SUM(SALARY) FROM EMP ;
SUM(SALARY)
_____________
150000
b) SELECT SUM(SALARY) FROM EMP
WHERE DEPTNO=20;
SUM(SALARY)
_____________
550000
2) COUNT()
The COUNT function count of all the values of the selected column excluding null. COUNT(*) count all value including null.
SELECT COUNT(col_name)
FROM table_name
WHERE condition;
Ex:
a)SELECT COUNT(DEPTNO) FROM EMP ;
COUNT(DEPTNO)
_________________
4
b) SELECT COUNT(*) FROM EMP ;
COUNT(DEPTNO)
_________________
5
3)MAX()
The MAX() function return maximum value or greatest value selected column.
SELECT MAX(col_name)
FROM table_name
WHERE condition;
SELECT MAX(SALARY) FROM EMP ;
MAX(SALARY)
------------------------
40000
3)MIN()
The MIN() function return minimum value or small value selected column.
SELECT MIN(col_name)
FROM table_name
WHERE condition;
SELECT MIN(SALARY) FROM EMP ;
MIN(SALARY)
------------------------
25000
3)AVG()
The AVG() function return average selected column.
SELECT AVG(col_name)
FROM table_name
WHERE condition;
SELECT AVG(SALARY) FROM EMP ;
Hi. I’m Designer of Blog Magic. I’m CEO/Founder of ThemeXpose. I’m Creative Art Director, Web Designer, UI/UX Designer, Interaction Designer, Industrial Designer, Web Developer, Business Enthusiast, StartUp Enthusiast, Speaker, Writer and Photographer. Inspired to make things looks better.
0 comments:
Post a Comment