Working With Having Clause in F#

Introduction

My previous article explaiined how to use Group by clause using Stored Procedure in Windows Forms application now I will describe in this article the Having Clause and how to use this clause in Windows in a Stored Procedure.

Having Clause

The having clause is used in combination with the Group By clause and the Select statement to filter the records that a Group By returns. The Having clause is generally used with the Group by clause. The Having clause applies to the records in a result set to filter the grouped data. The where clause does not work with the aggregate functions so we use the having clause instead of a where clause, for example sum, max, avg, min and so on.

Syntax:

Select col1, col2, col3,......col n, aggregateFunction (exp)

from table_name

where predicates

Group By col1, col2, col3,....Con

having Condition

Write the following procedure for creating a sample database and table in SQL Server:

Create database StudentResults

use StudentResults

create table StudentMarks

(

RollNo int,

FirstName varchar(max),

LastName varchar(max),

Subject varchar(max),

Marks int

)

Write the following procedure to insert values into the table columns:

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(1,'Pankaj','Lohani','Math',70)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(1,'Pankaj','Lohani','Physics',65)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(1,'Pankaj','Lohani','Chemestry',75)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(2,'Pravesh','Khanduri','Math',85)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(2,'Pravesh','Khanduri','Physics',60)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(2,'Pravesh','Khanduri','Chemestry',70)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(3,'Nimit','Joshi','Math',90)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(3,'Nimit','Joshi','Physics',60)

insert into StudentMarks (RollNo,FirstName,LastName,Subject,Marks) values(3,'Nimit','Joshi','Chemestry',75)

Write the following query to execute the table schema:

select * from StudentMarks

TableSchema

Having Clause using Aggregate Functions

Using Sum Function

Select FirstName, Sum(Marks) as 'Total Marks'

from StudentMarks

Group By FirstName

Having Sum(Marks)>200

SumFunction

Using Count Function

Select FirstName, Count(*) as 'No. of Subject'

from StudentMarks

Where Marks>60

Group By FirstName

Having Count(*)>1

CountFunction

Using Max Function

Select FirstName , Max(Marks) as 'Hightest Marks'

from StudentMarks

Group By FirstName

Having Max(Marks)<88

MaxFunction

Create the following Stored Procedure for students marks who have scored more than 200 marks:

Create proc ScoreHaving

begin

select FirstName, Sum(Marks) as 'Scored > 200'

from StudentMarks

Group by FirstName

Having Sum(Marks)>210

end

Create the following Stored Procedure for students average marks:

Create proc UsingWhereConditon

begin

SELECT FirstName, Avg(Marks) AS 'Average > 60'

FROM StudentMarks

where RollNo between 1 and 3

GROUP BY FirstName

HAVING Avg(Marks) > 60

end

Now I will show you how to bind the records of a database table and display them in a Windows Forms application. Use the following procedure to do that.

Step 1:

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

CreateApplication

Step 2:

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

SelectReferences

AddReferences

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 then click on "Ok".

ImportNamespaces

Write the following code to bind the data table into the datagrid and work with the Having Clause from the data source to the Windows Forms form:

Step 4:

open System

open System.Windows.Forms

open System.Drawing

open System.Data

open System.Data.SqlClient

//Create the new Window form

let havform=new Form(Text="Filter the Records")

//ConnectionString that establishes the connection with the specified database.

let constring = @"Data Source=MCNDESKTOP34;Initial Catalog=StudentResults;User ID=; Password="

let con=new SqlConnection(constring)

let com=new SqlCommand()

let adap=new SqlDataAdapter("select * from StudentMarks",constring)

let dt=new DataTable()

adap.Fill(dt) |>ignore

//DataGrid it shows the data in tabular format.

let datagrid=new DataGridView(Top=50,Left=0,Width=500,Height=250)

havform.Controls.Add(datagrid)

let grupbylbl=new Label(Text="Having Clause",Top=270,Width=120,Left=20)

havform.Controls.Add(grupbylbl)

datagrid.DataSource<-dt

let totalbtn=new Button(Top=350,Left=20,Width=220)

totalbtn.Text<-"Students Having Scored More Than 210"

totalbtn.ForeColor<-Color.Black

totalbtn.BackColor<-Color.Ivory

havform.Controls.Add(totalbtn)

totalbtn.Click.Add(fun _->

let com = new SqlCommand()

com.Connection <- con

com.CommandType <- CommandType.StoredProcedure

//ScoreHaving is a StoredProcedure it is shows the stdudent name who have scored more than 210 marks

com.CommandText <- "ScoreHaving"

let adapter = new SqlDataAdapter("ScoreHaving",con)

let dt = new DataTable()

adapter.Fill(dt) |>ignore

let gridview = new DataGridView(Top=380,Left=20,Width=240,Height=120)

havform.Controls.Add(gridview)

gridview.DataSource <- dt

)

let avgbtn=new Button(Top=510,Left=20,Width=180)

avgbtn.Text<-"Average Marks of Students"

avgbtn.BackColor<-Color.Ivory

avgbtn.ForeColor<-Color.Black

havform.Controls.Add(avgbtn)

avgbtn.Click.Add(fun _->

let com = new SqlCommand()

com.Connection <- con

com.CommandType <- CommandType.StoredProcedure

//UsingWhereCondition is a StoredProcedure it is applies for having clause using Where condtion

com.CommandText <- "UsingWhereConditon"

let adapter = new SqlDataAdapter("UsingWhereConditon",con)

let dt = new DataTable()

adapter.Fill(dt) |>ignore

let gridview = new DataGridView(Top=540,Left=20,Width=240,Height=120)

havform.Controls.Add(gridview)

gridview.DataSource <- dt

)

let exitbtn=new Button(Top=350,Left=470,Width=40)

exitbtn.Text<-"Exit"

exitbtn.ForeColor<-Color.Black

exitbtn.BackColor<-Color.Ivory

havform.Controls.Add(exitbtn)

exitbtn.Click.Add(fun exit->

havform.Close())

//Run the application

Application.Run(havform)

Step 5 :

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

f AfterDebug

Step 6 :

Now click on the button "Student having more than 210". It will display the name of the student with more than 210 marks. See the following figure:

Scored

Step 7 :

Now click on the button "Average Marks of Students"; it will display the average marks of the students. See the following figure:

Average

Summary

In this article you saw how to use the Having Clause in a Stored Procedure in a Windows Forms application and you saw the Group By Clause in my previous article.

Working With Having Clause in F#

Reactions

Comments