/*
STEP BY STEP: WHERE, GROUP BY, AND HAVING
by Jen McCown, http://www.Twitter.com/MidnightDBA

•	WHERE lets you limit the rows returned.
•	GROUP BY lets you perform aggregations (like count, sum, average, minimum, and maximum).
•	HAVING lets you limit the rows returned, based on aggregated values.

Blog link: http://www.midnightdba.com/Jen/2016/10/step-step-group/
Download link: http://www.midnightdba.com/Jen/wp-content/uploads/2016/10/WhereGroupHaving.txt

*/

---------------------------------
------------- SETUP -------------
---------------------------------
CREATE TABLE dbo.Livestock
       (
         Farm VARCHAR(1000)
       , AnimalType VARCHAR(1000)
       , AnimalName VARCHAR(1000)
       , AnimalWeightLbs INT
       );

INSERT  INTO dbo.Livestock
        ( Farm, AnimalType, AnimalName, AnimalWeightLbs )
VALUES  ( 'The Rocking J', 'Sheep', 'Andrew', 165 ),
        ( 'The Rocking J', 'Sheep', 'Bailey', 180 ),
        ( 'The Rocking J', 'Sheep', 'Caroline', 122 ),
        ( 'The Rocking J', 'Cow', 'Huberta', 1620 ),
        ( 'Lazy River', 'Sheep', 'Murray', 194 ),
        ( 'Lazy River', 'Cow', 'Zeke', 1704 ),
        ( 'Lazy River', 'Goat', 'Bill', 52 );


---- See the data in the table: 
SELECT  Farm
      , AnimalType
      , AnimalName
      , AnimalWeightLbs
FROM    dbo.Livestock
ORDER BY Farm DESC; -- Ignore the ORDER BY for now. That's another blog.

---------------------------------
------------- WHERE -------------
---------------------------------

---- WHERE - I only want data from The Rocking J:
SELECT  Farm, AnimalType, AnimalName, AnimalWeightLbs
FROM    dbo.Livestock
WHERE   Farm = 'The Rocking J';

---- WHERE - I only want to see sheep, from all farms: 
SELECT  Farm, AnimalType, AnimalName, AnimalWeightLbs
FROM    dbo.Livestock
WHERE  AnimalType = 'Sheep';

---- WHERE: I only want to see data on sheep from The Rocking J:
SELECT  Farm, AnimalType, AnimalName, AnimalWeightLbs
FROM    dbo.Livestock
WHERE   Farm = 'The Rocking J'
        AND AnimalType = 'Sheep';


---------------------------------
------------ GROUP BY -----------
---------------------------------

---- GROUP BY - Group data and not bother with any aggregations:  
SELECT  Farm, AnimalType
FROM    dbo.Livestock
GROUP BY Farm, AnimalType;

	/*
	ALTERNATE METHOD TO DO THIS: 

	SELECT  DISTINCT Farm, AnimalType
	FROM    dbo.Livestock;
	*/

---- GROUP BY - We want a COUNT of each kind of animal at each farm:

SELECT  Farm, AnimalType, COUNT(*) AS AnimalCount
FROM    dbo.Livestock
GROUP BY Farm, AnimalType;

---- GROUP BY - more aggregations:
SELECT  Farm, AnimalType, COUNT(*) AS AnimalCount
	, AVG(AnimalWeightLbs) AS AvgWeightLbs
	, MIN(AnimalWeightLbs) AS MinWeightLbs
	, MAX(AnimalWeightLbs) AS MaxWeightLbs
FROM    dbo.Livestock
GROUP BY Farm, AnimalType;


	---- Aside: GROUP BY and WHERE
	SELECT  Farm, AnimalType, COUNT(*) AS AnimalCount
		, AVG(AnimalWeightLbs) AS AvgWeightLbs
		, MIN(AnimalWeightLbs) AS MinWeightLbs
		, MAX(AnimalWeightLbs) AS MaxWeightLbs
	FROM    dbo.Livestock
	WHERE Farm = 'The Rocking J'
	GROUP BY Farm, AnimalType;


---------------------------------
------------ HAVING -------------
---------------------------------

---- HAVING - Pull back data on groups with an AVERAGE weight less than 200#:
SELECT  Farm, AnimalType, COUNT(*) AS AnimalCount
	, AVG(AnimalWeightLbs) AS AvgWeightLbs
FROM    dbo.Livestock
GROUP BY Farm, AnimalType
HAVING AVG(AnimalWeightLbs) < 200;


---- HAVING - Pull back data on groups with that have a COUNT of more than 1:
SELECT  Farm, AnimalType, COUNT(*) AS AnimalCount
	, AVG(AnimalWeightLbs) AS AvgWeightLbs
FROM    dbo.Livestock
GROUP BY Farm, AnimalType
HAVING COUNT(*) > 1;


---------------------------------
------------- BONUS -------------
---------------------------------

---- Take aggregations on the entire table, without a GROUP BY. That's the AVG of all animals in the table!
SELECT  AVG(AnimalWeightLbs) AS AvgWeightLbs
FROM    dbo.Livestock;


---- Take aggregations on the entire table, without a GROUP BY but using WHERE. That's the AVG of all SHEEP in the table!
SELECT  AVG(AnimalWeightLbs) AS AvgWeightLbs
FROM    dbo.Livestock
WHERE AnimalType = 'Sheep';

---- If we put "AnimalType" in the select list now, without the GROUP BY, we'll get an ERROR: 
SELECT  AnimalType, AVG(AnimalWeightLbs) AS AvgWeightLbs
FROM    dbo.Livestock
WHERE AnimalType = 'Sheep';
	-- Msg 8120, Level 16, State 1, Line 134
	-- Column 'dbo.Livestock.AnimalType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

	-- Fix it: 
	SELECT  AnimalType, AVG(AnimalWeightLbs) AS AvgWeightLbs
	FROM    dbo.Livestock
	WHERE AnimalType = 'Sheep'
	GROUP BY AnimalType;

---- GROUP BY fields that aren't in the SELECT statement:
SELECT  AVG(AnimalWeightLbs) AS AvgWeightLbs
FROM    dbo.Livestock
GROUP BY Farm;


---- WHERE, GROUP BY, and HAVING: 
SELECT  Farm, AnimalType, MIN(AnimalWeightLbs) AS MinWeightLbs
FROM    dbo.Livestock
WHERE   Farm = 'The Rocking J'
GROUP BY Farm, AnimalType
HAVING MIN(AnimalWeightLbs) > 150;

---------------------------------
------------ CLEANUP ------------
---------------------------------
DROP TABLE dbo.Livestock