Class Fifteen: Text(Group by statement, Having statement and subquery)

HAVING STATEMENT

HAVING” is used to filter the results of a “GROUP BY” statement. The “HAVING” statement comes after the “GROUP BY” statement and before the “ORDER BY” statement. It has a similar syntax to the “WHERE” statement, but it’s only used with “GROUP BY”. You can use any of the comparison operators (“=”, “<“, “>”, “>=”, “<=”, “LIKE”, etc.) in a “HAVING” statement.  It is used to filter an aggregators

Example

1). SELECT  SUM([Tax Amount])

FROM [Fact].[Transaction]

JOIN [Fact].[Purchase]

ON [Fact].[Purchase].[Date Key]=[Fact].[Transaction].[Date Key]

GROUP BY [Tax Amount]

HAVING SUM([Tax Amount])>1000

2). SELECt Sum([OrderTotal])

FROM [dbo].[Orders]

JOIN [dbo].[Customers]

ON [Customers].CustomerID=[dbo].[Orders].CustomerID

GROUP BY OrderTotal

HAVING OrderTotal <500

SUBQUERIES

Subqueries, also known as nested queries, allow you to use the result of one query within another query. There are different types of subqueries:

Subqueries are a powerful tool that allow you to use the results of one query as part of another query. Subqueries are sometimes called “nested queries” because they’re nested within another query. In the example we just looked at, the subquery is nested within the main query.

1). SELECT  [Stock Item Key], [Supplier Key],  [Ordered Quantity]

FROM [Fact].[Purchase]

WHERE [Stock Item Key] IN (SELECT [Stock Item Key] FROM [Fact].[Order]  WHERE [Unit Price]< 150)

2). SELECT [Customer Key],[Bill To Customer] ,[Primary Contact]

 FROM [Dimension].[Customer]

 WHERE [Customer Key] IN (SELECT [Customer Key] FROM [Fact].[Sale]

 WHERE Profit =800 )

3). SELECT CustomerID, CustomerName,Phone

FROM [dbo].[Customers]

WHERE CustomerID IN( SELECT CustomerID FROM [dbo].[Orders]

WHERE OrderTotal <500)