Use 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.