/* HAVING example from http://idealprogrammer.com 
 PURPOSE: HAVING specifies search condition for group or aggregate. HAVING is 
          usually used with GROUP BY, but when it is not, it functions in 
          the same way that the WHERE clause does.
 
 
 SYNTAX: HAVING search_condition
 
 SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			 column1 AS displayname1, displayname2 = column_name2, column_name3 displayname3  
			[ INTO new_table ] 
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE search_condition ] 
			[ GROUP BY ] 
			[ HAVING  search_condition  ] 
			[ ORDER BY  order_expression [ ASC | DESC ] ] 
*/
 
 
CREATE TABLE people(
     ID          int,
     firstname   varchar (20),
     lastname    varchar (20),
     statecode   varchar (2),
     alive       bit,
     height      int )
 GO
 
 
 
 
 
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (1,  'Paul', 'Revere', 'AL', 0, 72)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (2,  'John', 'Lennon', 'NY', 0, 69)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (3,  'Steve', 'Martin', 'NY', 1, 75)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (4,  'George', 'Washington', 'VA', 0, 75)
 GO
 
 
 -- 1. HAVING used with GROUP BY
 SELECT 'Example #1' as Example, statecode, AVG(height) as Avg_Height, COUNT(*) as Count  
  FROM people  
  GROUP BY statecode
  HAVING AVG(height) > 72
 GO
 
 
 
 
 
 
 DROP TABLE people;
 
 GO

/* HAVING example from http://idealprogrammer.com PURPOSE: HAVING specifies search condition for group or aggregate. HAVING is usually used with GROUP BY, but when it is not, it functions in the same way that the WHERE clause does. SYNTAX: HAVING search_condition SELECT [ ALL | DISTINCT ] [TOP ( expression ) [PERCENT] [ WITH TIES ] ] column1 AS displayname1, displayname2 = column_name2, column_name3 displayname3 [ INTO new_table ] [ FROM { table_source } [ ,...n ] ] [ WHERE search_condition ] [ GROUP BY ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] */ CREATE TABLE people( ID int, firstname varchar (20), lastname varchar (20), statecode varchar (2), alive bit, height int ) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (1, 'Paul', 'Revere', 'AL', 0, 72) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (2, 'John', 'Lennon', 'NY', 0, 69) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (3, 'Steve', 'Martin', 'NY', 1, 75) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (4, 'George', 'Washington', 'VA', 0, 75) GO -- 1. HAVING used with GROUP BY SELECT 'Example #1' as Example, statecode, AVG(height) as Avg_Height, COUNT(*) as Count FROM people GROUP BY statecode HAVING AVG(height) > 72 GO DROP TABLE people; GO