SQL: Having in the Group By
Author: jason
Date: 2014-01-23 12:15:23
Category: Musing

Using the HAVING clause can save you from manually searching through SQL results if you are looking only for certain sums or counts.

In the 1st Select we are using SUM to add up the number field. We would need to visually review or put the results into Excel if we wanted to find where the SUM was equal to One. Using the HAVING clause in the 2nd query shows we can let SQL do the work for us.





Code

DECLARE @Temp AS TABLE ([id] BIGINT, [name] VARCHAR(10), [number] INT)
INSERT INTO @Temp SELECT 1, 'Apple', 10
INSERT INTO @Temp SELECT 2, 'Peach', 1
INSERT INTO @Temp SELECT 3, 'Apple', 7
INSERT INTO @Temp SELECT 4, 'Peach', NULL
INSERT INTO @Temp SELECT 4, 'Peach', NULL
INSERT INTO @Temp SELECT 5, 'Orange', NULL

SELECT [t].[name], SUM([t].[number]) AS [number] FROM @Temp t
GROUP BY [t].[name]

SELECT [t].[name], SUM([t].[number]) AS [number] FROM @Temp t
GROUP BY [t].[name]
HAVING SUM([t].[number]) = 1




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009