Class Fifteen; ,(GROUP BY STATEMENT AND HAVING STATEMENT SQL)

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]

2). SELECt Sum([OrderTotal])

FROM [dbo].[Orders]

JOIN [dbo].[Customers]

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

GROUP BY OrderTotal

GROUP BY” is only used with aggregate functions like “SUM”, “MIN”, “MAX”, “AVG”, and “COUNT”. You can’t use “GROUP BY” with a regular function that returns a single value, like “ID” or “NAME”. Only functions that return a set of values can be used with “GROUP BY”

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