Aggregate Functions in F#

Introduction

In this article you will see and learn about the aggregate functions in a Windows Forms application.

Aggregate Functions

Aggregate functions perform calculations on a set of values and returns a single value for the specified column. Aggregate functions ignore null values. There are various types of aggregate functions such as the following.

• Max( )
• Min( )
• Count( )
• Sum( )
• AVG( )
• GetDate( )

Max ( )

The Max function returns the maximum value of the specified column name. It ignores null values.

syntax: Select max(columnname) from tablename

Min ( )

The Max function returns the minimum value of the specified column name. It ignores null values.

syntax: Select min(columnname) from tablename

Count ( )

The Count unction returns the number of rows available in a specified column name then returns the single value. It is also ignores null values.

syntax: Select count(columnname) from tablename

Sum ( )

The Sum function returns the value of the sum of the values in a specified column name and returns a single value. It ignores null values.

syntax: Select Sum(columnname) from tablename

AVG ( )

The AVG function returns the average of the values in a specified column name and returns a single value. It ignores null values.

syntax: Select Avg(columnname) from tablename

GetDate ( )

The Getdate function retruns the current system time and date.

syntax: Select getdate()

Use the following procedure to create the database and table in SQL Server:

Create Database Employee
use Employee
create table EmployeeSalary
(
EmpId int primary key,
EmpName varchar(max),
Salary money 
)

Use the following procedure to insert the duplicate values in the database table in SQL Server:

insert into EmployeeSalary values(101,'Pankaj',20000)
insert into EmployeeSalary values(102,'Nimit',15000)
insert into EmployeeSalary values(103,'Pravesh',16000)
insert into EmployeeSalary values(104,'Amit',12000)
insert into EmployeeSalary values(105,'Ravi',25000)
insert into EmployeeSalary values(106,'Ainul',30000)

Use the following procedure to execute the query for a database table in SQL Server:

select * from EmployeeSalary 

 

Now  I will show you how to use aggregate functions in Windows Forms application. Let's use the following procedure.

Step 1

Open Visual Studio then select "Create New Project" --> "F# Console Application".

Step 2

Now go the Solution Explorer on the right side of the application. Right-click on "References" and select "Add references".

Step 3

After selecting "Add References", in the framework template you need to select "System.Windows.Forms", "System.Drawing" and "System.Data" while holding down the Ctrl key and click on "Ok."

Step 4

Use the following code showing how to use aggregate functions in a Windows Forms application.

open System
open System.Windows.Forms
open System.Data
open System.Data.SqlClient
open System.Drawing
let aggfform=new Form(Text="Aggrigate Functions")
aggfform.BackColor<-Color.DarkGray
let ffont=new Font("Arial", 9.75F,FontStyle.Regular, GraphicsUnit.Point)
let constring = @"Data Source=MCNDESKTOP34;Initial Catalog=Employee;User ID=sa; Password=mcn@123" 
let adap=new SqlDataAdapter("select * from EmployeeSalary",constring)
let dt=new DataTable()
adap.Fill(dt) |>ignore
let datagrid=new DataGridView(Top=20,Left=40,Width=440,Height=200)
datagrid.DataSource<-dt 
let maxbtn=new Button(Text="MAX SALARY",Top=240,Left=0,Width=100)
let minbtn=new Button(Text="MIN SALARY",Top=280,Left=0,Width=100)
let countbtn=new Button(Text="COUNT EMPLOYEES",Top=320,Left=0,Width=120)
let sumbtn=new Button(Text="SUM OF SALARIES",Top=360,Left=0,Width=120)
let avgbtn=new Button(Text="AVG OF SALARIES",Top=400,Left=0,Width=120)
let getdatebtn=new Button(Text="GETDATE",Top=440,Left=0,Width=100)
maxbtn.BackColor<-Color.Ivory
minbtn.BackColor<-Color.Ivory
countbtn.BackColor<-Color.Ivory
sumbtn.BackColor<-Color.Ivory
avgbtn.BackColor<-Color.Ivory
getdatebtn.BackColor<-Color.Ivory
aggfform.Controls.Add(datagrid)
let valuemax=new Label(Top=240,Left=140,Height=30,BorderStyle=BorderStyle.FixedSingle)
let valuemin=new Label(Top=280,Left=140,Height=30,BorderStyle=BorderStyle.FixedSingle)
let valuecount=new Label(Top=320,Left=140,Height=30,BorderStyle=BorderStyle.FixedSingle)
let valuesum=new Label(Top=360,Left=140,Height=30,BorderStyle=BorderStyle.FixedSingle)
let valueavg=new Label(Top=400,Left=140,Height=30,BorderStyle=BorderStyle.FixedSingle)
let valuegetdate=new Label(Top=440,Left=140,Height=30,BorderStyle=BorderStyle.FixedSingle)
aggfform.Controls.Add(valuemax)
aggfform.Controls.Add(valuemin)
aggfform.Controls.Add(valuecount)
aggfform.Controls.Add(valuesum)
aggfform.Controls.Add(valueavg)
aggfform.Controls.Add(valuegetdate)
aggfform.Controls.Add(maxbtn)
aggfform.Controls.Add(minbtn)
aggfform.Controls.Add(countbtn)
aggfform.Controls.Add(sumbtn)
aggfform.Controls.Add(avgbtn)
aggfform.Controls.Add(getdatebtn)
//MAX FUNCTION
maxbtn.Click.Add(fun _->
let con = new SqlConnection(constring)
con.Open()
let com = new SqlCommand("Select max(Salary) from EmployeeSalary",con)
com.Connection <- con
valuemax.Text<-com.ExecuteScalar().ToString()
)
//MIN FUCTION
minbtn.Click.Add(fun _->
let con = new SqlConnection(constring)
con.Open()
let com = new SqlCommand("Select min(Salary) from EmployeeSalary",con)
com.Connection <- con
valuemin.Text<-com.ExecuteScalar().ToString()
)
//COUNT FUNCTION
countbtn.Click.Add(fun _->
let con = new SqlConnection(constring)
con.Open()
let com = new SqlCommand("Select Count(Salary) from EmployeeSalary",con)
com.Connection <- con
valuecount.Text<-com.ExecuteScalar().ToString()
)
//SUM FUNCTION
sumbtn.Click.Add(fun _->
let con = new SqlConnection(constring)
con.Open()
let com = new SqlCommand("Select Sum(Salary) from EmployeeSalary",con)
com.Connection <- con
valuesum.Text<-com.ExecuteScalar().ToString()
)
//AVG FUNCTION
avgbtn.Click.Add(fun _->
let con = new SqlConnection(constring)
con.Open()
let com = new SqlCommand("Select avg(Salary) from EmployeeSalary",con)
com.Connection <- con
valueavg.Text<-com.ExecuteScalar().ToString()
)
//GETDATE FUNCTION
getdatebtn.Click.Add(fun _->
let con = new SqlConnection(constring)
con.Open()
let com = new SqlCommand("Select getdate()",con)
com.Connection <- con
valuegetdate.Text<-com.ExecuteScalar().ToString()
)
Application.Run(aggfform)

Step 5

Debug the application by pressing F5 to execute the Windows Forms application. After debuging the application the output will be as in the following figure:

Step 6

Now click on the "Max Salary and Min Salary" button as in the following figure.

Step 7

Now click on "Count Employees and Sum of Salaries" button as in the following figure.

Step 8

Now click on the "Avg and Getdate" button as in the following figure.

Summary

This article has explained the aggregate functions and how to use aggregate functions in a Windows Forms application.