Tuesday, November 20

Aggregate Functions - SQL



Aggregate Functions
Aggregating data includes counting, totaling, and averaging data. Aggregate functions are as follows:
  • Count( )
  • Count_Big( )
  • Count(distinct)
  • Min( )
  • Max( )
  • Sum( )
  • Avg( )
  • StDev( )

COUNT( ) & COUNT_BIG

To count number of records in a table
Using COUNT function you count the records in a table as well as count in a column.

Syntax:

  • Select COUNT(*) from table_name "
  • Select COUNT(colum_name) from table_name "

Example:
  •  Select COUNT(*) from T1 "                  // Display number if records in table T1
  •  " Select COUNT(C1) from T1 "          // Display number if records column C1 in table T1,                             
Note: However it will not count null values in a column but will count duplicates. This happens when you use the second query. Now When you Count(*), It will display the count including null values and duplicates.

If you are counting records more than a billion then use Count_Big function.
  • Select Count_Big (*)  from table_name "


I have explained Count(Distinct) and Count_Big function in earlier post.Click on count(distinct) to go to that post.




Min( )
To display the minimum value from the records we use this function. This function can be applied to an alphabetic column as well as numeric column

Syntax:

  • " Select MIN( Column_name) from Table_name "
Note: Min() function is applied on a specific column not to whole table, i.e. Min(*) is not valid.



Min() on Alphabetic column

Example: Consider a column Emp_name in table Employee containing names of the employees as shown below
Now when MIN() is applied on this column, The output will be
  • " Select MIN ( Emp_name) from Employee "
Result:

Emp_name is a nvarchar data type. It’s a string, and Min() applied to a string will return the first one in alphabetical order.





Min() function applied to a numeric column.

Example: Consider a column Salary in table Employee containing salary of the employees as shown below
Now when MIN() is applied on this column, The output will be
  • " Select MIN ( Salary) from Employee "
Result:
Salary is a money data type. It’s numeric, and Min() applied to a number will return the lowest number. If the records have a null value in the Salary column—Min() ignores null values.



Max( )
Max() function operating on a string.
  • " select MAX ( Emp_name ) from Employee "
Result:
It returns the last one in the alphabetical order.


Max() function on a numeric column.
  • " select MAX (Salary ) from Employee "
Result:
This is the highest salary in column.

Now if you want to know the highest salary and to whom it is given, Try this quey out but it will result in error.
  • " select MAX (Salary ), Emp_name from Employee "
Result: Error, Why because we cannot apply aggregate function as part of a normal Select column list. To Solve this, Check this quey out:
  • select  Salary , Emp_name from Employee where Salary = 25000 "
Result of this Query will be as we wished,
 but here in this query it assumes that we all ready know the maximum salary, which we do not know. To solve this we need to write two queries.
  • " select  Salary , Emp_name from Employee where Salary = (select MAX (Salary )    
            from Employee) "
Result:


Now we got the result as we wished. This is writing a query with in a another query or called as Nested Queries.





Sum( )
Sum() function operating on a numeric column.
To find the total salary, The query can be written as:
  • select SUM(Salary) as Total_salary from Employee "
Result:
Note: If sum( ) function tried on String column it will result in error.




AVG( )
Avg( ) work with numeric columns and does not work with strings
  • " select AVG(Salary) from Employee"
Result:
It gives the average of the salary.





StDev()
StDev() is used to work out standard deviations
  • " select STDEV(Salary) from Employee "
Result:
Note:To perform aggregations against subsets of tables (i.e. to calculate subtotals as well as grand totals) we use GROUP BY clause along with Aggregate Functions.