I have already explained you  regarding  RANK(),  DENSE_RANK()  and ROW_NUMBER(). Today I am going to explain some of the aggregate functions in sql server.

aggregate functions in sql server are used to perform calculation on columns which will return the single value. Below are the some of the most used aggregate functions. They can be used as a expressions either in SELECT statement or HAVING clause

AVG() : It will return the average of column to be specified.

SUM() : It will return the sum of column to be specified.

COUNT() : It will return the number of items in a group.

MIN() : It will return the minimum value of column to be specified.

MAX() : It will return the maximum value of column to be specified.

Now lets go through the example for each of the above specified functions. So lets create a one sample table sales with some records in it.

CREATE TABLE [dbo].[Sales](
	[Product] [varchar](200) NULL,
	[Category] [varchar](100) NULL,
	[Price] [decimal](18, 3) NULL
)

INSERT [dbo].[Sales] ([Product], [Category], [Price]) VALUES ('Samsung galaxy Y', 'Electronics', CAST(2000.000 AS Decimal(18, 3)))
INSERT [dbo].[Sales] ([Product], [Category], [Price]) VALUES ('Nokia 800', 'Electronics', CAST(1500.000 AS Decimal(18, 3)))
INSERT [dbo].[Sales] ([Product], [Category], [Price]) VALUES ('Puma Atom', 'Footwear', CAST(1200.000 AS Decimal(18, 3)))
INSERT [dbo].[Sales] ([Product], [Category], [Price]) VALUES ('Arrow 1234', 'Clothing', CAST(670.500 AS Decimal(18, 3)))
INSERT [dbo].[Sales] ([Product], [Category], [Price]) VALUES ('Reebok RealFlex', 'Footwear', CAST(999.000 AS Decimal(18, 3)))
INSERT [dbo].[Sales] ([Product], [Category], [Price]) VALUES ('Lee', 'Clothing', CAST(865.340 AS Decimal(18, 3)))

So my final table is something like below image.

 

Now lets use all the aggregate function on this table.

Function	Query	Output
AVG() : Average of column price	SELECT AVG(PRICE) FROM SALES	1205.806666
SUM() : To find total sales from above table	SELECT SUM(PRICE) FROM SALES	7234.840
COUNT() : Count of category	SELECT COUNT(CATEGORY) FROM SALES	6
COUNT() : Count of distinct category	SELECT COUNT(DISTINCT CATEGORY) FROM SALES	6
COUNT() : Count of all the records	SELECT COUNT(*) FROM SALES	6
MIN() : Minimum price from sales table	SELECT MIN(PRICE) FROM SALES	670.500
MAX() : Maximum Price from sales table	SELECT MAX(PRICE) FROM SALES	2000.000