All Type Coding

Search Here

Having Clause in sql server.

HAVING Clause

The Having clause operates only on group rows of tables and act as a filter like as where clause. We use having clause to filter data that we get from group by clause. To use having clause we need to use group by clause first.HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Syntax:
                   SELECT column_name1, aggregate_function(column2_name)
                   FROM table_name
                   WHERE column_name = value
                   GROUP BY column_name
                   HAVING aggregate_function(column2_name) operator value;


Here we will see an example of Having clause.we have a table Employee1 for this demo




Query without having clause.

Select Name,Salary,Gender
count(*)Total from Employee1
group by Name,Salary,Gender

output will be:

















.By the above query output like above image It's showing a total
Column which tell us How many people having the same name and Gender

4. Now we want to select  only that person which having the same name more than one people.

Select Name,Salary,Gender,
count(*)Total from Employee1
where id between 1 and 7
group by Name,Salary,Gender

having count(*)>1
OR

Select Name,Salary,Gender,
count(*)Total from Employee1
where id between 1 and 7
group by Name,Salary,Gender

having count(Name)>1

By this query we get only that data from the table which having total greater than 1
like below.


















with the help of having clause and group by we can easily filter the  data  from the database table

No comments :

Post a Comment