






--Grouping example with GROUP BY
select * from customer 

--Let Get the total balance fir a city
select sum(balance) as TotalBalance from customer
group by city

--But we need to include the cities wich totaled, so we'll include the city column
select sum(balance) as TotalBalance, City from customer
group by city

--Lets try to show the street also
select sum(balance) as TotalBalance, City,street  from customer
group by city
--ERROR: The above statement will give you an error. Why?
-- The street column  is not included in the grouping.
--So when doing a grouping, the columns displayed need to either:
	-- 1) Be part of the group by statement OR
	-- 2) Be the argument of an aggregate function (SUM,AVG,COUNT...etc)

select count(*) from customer

select count(customernum) from customer


--Lets get the total number of records per city
select count(*) as NumberofRecords,city from customer
group by city

--Since the COUNT function is working on counting the entire record, 
--the column which we use as an argument in count is irrelevant

--This statement
select count(customernum) as NumberofRecords,city from customer
group by city

-- yields the same result as

select count(customerName) as NumberofRecords,city from customer
group by city

--Filter your grouping with HAVING

--Using the HAVING statements, performs your grouping first, then filters the records from the grouped set
select sum(balance) as TotalBalance, City from customer
group by city
having sum(balance)  > 10000

select * from customer

--Can I add a WHERE filter? Yes, but that is different than HAVING. See the following statement
select sum(balance) as TotalBalance, City 
from customer 
where customername = 'Kline''s'  
group by city 
having sum(balance)  > 10000 
--The previous statement, first filtered the records by customer name, 
--then grouped those records, then filtered the grouped set by the city of fillmore

--In the Henry Books tables, do the following: 
--Find the average price of books, grouped by book code in branch number 1, 
--whose average price is less than $10
select * from copy

select avg(price) as AvgPrice , bookcode
from copy
where Branchnum = 1
group by bookcode
having avg(price) < 10




