-- John Aromando -- SQL Aggregates --

use ogcPubLev1

select state, City, count(custnum) as CountofCustomers
from Customers
group by state, City

-- distinct similar to grouping
select distinct Customers.custnum, custname, qty
from sales, Customers
where Sales.custnum = Customers.custnum
order by custname

select state, COUNT(custnum)as CountOfCustomers
from customers
group by state
with cube --grand total

select
	state, 
	SUM(qty)as SumQty,
	MAX(qty) as MaxQty,
	MIN(qty)as MinQty,
	AVG(qty) as AvgQty,
	Count(qty)as CountQty
from Sales as s
inner join Titles as t on s.partnum = t.partnum
inner join Customers as c on s.custnum = c.custnum
group by state
having SUM(qty) <> 0
order by state