Aggregate functions in SQL

We have below aggregate functions in SQL:

Aggregate Function

COUNT

SUM

AVG

MAX

MIN

COUNT(*)

COUNT_BIG

STDEV

a. COUNT:

Returns the total number of values in a column excluding NULL values

SELECT

     COUNT(EMPID)

FROM

     Employee

b. SUM:

Returns the sum of values in a given column

SELECT

     SUM(Salary)

FROM

     Employee

c. AVG:

Returns the average of values in a given column

SELECT

     AVG(Salary)

FROM

     Employee

d. MAX:

Returns the maximum value in a given column

SELECT

     MAX(Salary)

FROM

     Employee

e. MIN:

Returns the minimum value in a given column

SELECT

     MIN(Salary)

FROM

     Employee

f. COUNT(*)

Returns number of rows in a table

SELECT

     COUNT(*)

FROM

     Employee

g. COUNT_BIG

Returns number of rows in a table and it is same as COUNT, the only difference is COUNT_BIG returns bigint data type value where as COUNT returns int data type value.

It will be used whenever the number of rows values doesn't fit into integer value
h. STDEV:
Returns the standard deviation for the specified column

SELECT

     STDEV(Salary)

FROM

     Employee

To understand the difference between COUNT(*) and COUNT see the below query fired on sample data:

SELECT * FROM Employee

SELECT COUNT(*) AS CountAgg, COUNT(LastName) AS ColCount FROM Employee

If we see the above screen shot, COUNT(Lastname) didn't count NULL values that is the reason it returned 4 instead of 5