Having Clause in SQL Server

 Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.

Because the WHERE clause is evaluated before GROUP BY, you cannot use WHERE to pare down results of the grouping (typically an aggregate function, such as COUNT(*)). To meet this need, the HAVING clause can be used.

[Logical Processing Order of SELECT statement]

Let's see the below example for more clarity.

DECLARE @orders TABLE(OrderID INT, Name NVARCHAR(100))
INSERT INTO @orders VALUES
( 1, 'Matt' ),
( 2, 'John' ),
( 3, 'Matt' ),
( 4, 'Luke' ),
( 5, 'John' ),
( 6, 'Luke' ),
( 7, 'John' ),
( 8, 'John' ),
( 9, 'Luke' ),
( 10, 'John' ),
( 11, 'Luke' )

If we want to get the number of orders each person has placed, we would use

SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name

Output:

Name	Orders
Matt	2
John	5
Luke	4

SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name
HAVING COUNT(*) > 2

Output:

Name	Orders
John	5
Luke	4

Note that, much like GROUP BY, the columns put in HAVING must exactly match their counterparts in the SELECT

statement. If in the above example we had instead said

SELECT Name, COUNT(DISTINCT OrderID)

our HAVING clause would have to say

HAVING COUNT(DISTINCT OrderID) > 2

Group by in SQL Server with example

A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.

The GROUP BY clause allows you to arrange the rows of a query in groups. The groups are determined by the columns that you specify in the GROUP BY clause.

Let's understand this by simple example:

Example: Group by on Single Column

Table Name : Order

CustomerId	ProductId	Quantity	Price
1	2	5	100
1	3	2	200
1	4	1	500
2	1	4	50
3	5	6	700

When grouping by a specific column, only unique values of this column are returned.

SELECT customerId
FROM orders
GROUP BY customerId;

Output:

CustomerId

1

2

3

Aggregate functions like count() apply to each group and not to the complete table:

SELECT customerId,
COUNT(productId) as numberOfProducts,
SUM(price) as totalPrice
FROM orders
GROUP BY customerId;

OutPut:

customerId	numberOfProducts	totalPrice
1	3	800
2	1	50
3	1	700

Example: Group by multiple column.

One might want to GROUP BY more than one column

Let's see the below example. 

declare @temp table(age int, name varchar(15))
insert into @temp
select 18, 'matt' union all
select 21, 'matt' union all
select 21, 'matt' union all
select 18, 'luke' union all
select 18, 'luke' union all
select 21, 'luke' union all
select 18, 'luke' union all
select 21, 'luke'

SQL Code:

SELECT Age, Name, count(1) count
FROM @temp
GROUP BY Age, Name

Output:

Age	Name	count
18	luke	3
21	luke	2
18	matt	1
21	matt	2

SQL Cross Join

 A cross join is a join operation that produces the Cartesian product of two or more tables.

In Math, a Cartesian product is a mathematical operation that returns a product set of multiple sets.

For example, with two sets A {x,y,z} and B {1,2,3}, the Cartesian product of A x B is the set of all ordered pairs (x,1), (x,2), (x,3), (y,1) (y,2), (y,3), (z,1), (z,2), (z,3).

Suppose the A table has n rows and the B table has m rows, the result of the cross join of the A and B tables have n x m rows.

Syntax:

The syntax of the CROSS JOIN in SQL will look like the below syntax:

SELECT ColumnName_1,ColumnName_2,ColumnName_N

FROM [Table_1] CROSS JOIN [Table_2]

Or we can use the following syntax instead of the previous one. This syntax does not include the CROSS JOIN keyword; only we will place the tables that will be joined after the FROM clause and separated with a comma.

SELECT ColumnName_1,ColumnName_2,ColumnName_N FROM [Table_1],[Table_2]

The result set does not change for either of these syntaxes. In addition, we must notice one point about the CROSS JOIN. Unlike the INNER JOIN, LEFT JOIN and FULL OUTER JOIN, the CROSS JOIN does not require a joining condition

Example:

Let's consider the below table.

CREATE TABLE Meals(MealName VARCHAR(100)) CREATE TABLE Menus(MenuName VARCHAR(100))

INSERT INTO Menus VALUES('Chicken Tikka'), ('Egg Omlet'), ('Chicken Noodles')

INSERT INTO Meals VALUES('Non Veg'), ('Starter'), ('Fast Food')

SQL Code:

SELECT * from Meals CROSS JOIN Menus

Output:

MealName	MenuName
Non Veg	Chicken Tikka
Starter	Chicken Tikka
Fast Food	Chicken Tikka
Non Veg	Egg Omlet
Starter	Egg Omlet
Fast Food	Egg Omlet
Non Veg	Chicken Noodles
Starter	Chicken Noodles
Fast Food	Chicken Noodles

Please visit related article below:

• INNER JOIN
• LEFT OUTER JOIN: 
• RIGHT OUTER JOIN: 
• FULL OUTER JOIN: 
• Self Join
• CROSS JOIN

SQL Self Join with example

The self join is a popular special case of the SQL JOIN. While most JOINs link two or more tables with each other to present their data together, a self join links a table to itself. This is usually done by joining a table to itself just once within a SQL query, but it is possible to do so multiple times within the same query.

When referring to the same table more than once in an SQL query, we need a way to distinguish each reference from the others. For this reason, it is important to use aliases to uniquely identify each reference of the same table in an SQL query. As a good practice, the aliases should indicate the role of the table for each specific reference in a query.

Let's see below few examples where we will see the usage of self join.

Example: Find the hierarchies

This type of table structure is very common in hierarchies. Now, to show the name of the manager for each employee in the same row, we can run the following query:

Table: Employee

Id	FullName	Salary	ManagerId
1	John Smith	10000	3
2	Jane Anderson	12000	3
3	Tom Lanon	15000	4
4	Anne Connor	20000
5	Jeremy York	9000	1

SQL Code:

SELECT

    employee.Id as EmpId,

        employee.FullName as EmpFullName,

        employee.ManagerId,

        manager.FullName as ManagerName

FROM Employees employee

JOIN Employees manager

ON employee.ManagerId = manager.Id

Output:

EmpId	EmpFullName	ManagerId	ManagerName
1	John Smith	3	Tom Lanon
2	Jane Anderson	3	Tom Lanon
3	Tom Lanon	4	Anne Connor
5	Jeremy York	1	John Smith

Example: Find the pair among the colleagues

Suppose we need to generate all possible pairs among the colleagues so that everyone has a chance to talk with everyone else at the company introductory evening.

Table: Colleagues 

Id	FullName	Age
1	Bart Thompson	43
2	Catherine Anderson	44
3	John Burkin	35
4	Nicole McGregor	29

SQL Code:

SELECT

    teammate1.FullName as Teammate1FullName,

    teammate1.Age as Teammate1Age,

        teammate2.FullName as Teammate2FullName,

    teammate2.Age as Teammate2Age

FROM Colleagues teammate1

JOIN Colleagues teammate2

ON teammate1.FullName <> teammate2.FullName

Output:

Teammate1FullName	Teammate1Age	Teammate2FullName	Teammate2Age
Catherine Anderson	44	Bart Thompson	43
John Burkin	35	Bart Thompson	43
Nicole McGregor	29	Bart Thompson	43
Bart Thompson	43	Catherine Anderson	44
John Burkin	35	Catherine Anderson	44
Nicole McGregor	29	Catherine Anderson	44
Bart Thompson	43	John Burkin	35
Catherine Anderson	44	John Burkin	35
Nicole McGregor	29	John Burkin	35
Bart Thompson	43	Nicole McGregor	29
Catherine Anderson	44	Nicole McGregor	29
John Burkin	35	Nicole McGregor	29

Please visit related article below:

• INNER JOIN
• LEFT OUTER JOIN: 
• RIGHT OUTER JOIN: 
• FULL OUTER JOIN: 
• Self Join
• CROSS JOIN