SQL AGGREGATES/GROUP BY/HAVING
================================

Min, Max, Count, Avg, Sum, Stdev

SELECT
   min(firstaired)
   , max(firstaired)
   , count(*)           --- number of tuples
   , count(firstaired)  --- number of values this attribute has, not null
FROM
   episodes ;


SELECT
   count(*)
   , count(distinct baker)
   , min(episodeid)
   , max(episodeid)
FROM
   results
WHERE
   result = 'star baker' ;


SELECT
   count(*)
   , min(episodeid)
   , max(episodeid)
FROM
   results
WHERE
   result = 'star baker'
   and baker = 'Rahul';

--------------------------------------

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

SELECT
   baker
FROM
   results
WHERE
   result = 'star baker'
ORDER BY
   baker;


  baker  
---------
 Briony
 Dan
 Kim-Joy
 Kim-Joy
 Manon
 Rahul
 Rahul
 Ruby
 Ruby



SELECT
   baker
   , count(*)
FROM
   results
WHERE
   result = 'star baker'
GROUP BY
   baker
ORDER BY
   baker
;


G1
 Briony
G2
 Dan
G3 
 Kim-Joy
 Kim-Joy
G4 
 Manon
G5 
 Rahul
 Rahul
G6 
 Ruby
 Ruby


SELECT    
   episodeid
   , count(*)  as numbakers
FROM
   showstoppers
WHERE
   lower(make) like '%chocolate%'
GROUP BY
   episodeid
;


SELECT    
   baker
   , count(*)  as numepisodes
FROM
   showstoppers
WHERE
    lower(make) like '%chocolate%'
GROUP BY
   baker
;


SELECT
   b.baker
   , b.fullname
   , count(*) as numepisodes
   , min(e.firstaired)
   , max(e.firstaired)
FROM
   showstoppers ss
   , episodes e
   , bakers b
WHERE
   ss.baker = b.baker
   and ss.episodeid = e.id
   and lower(ss.make) like '%chocolate%'
GROUP BY
   b.baker;



SELECT
   baker
   , count(*)
FROM
   results
WHERE
   result = 'star baker'
GROUP BY
   baker
HAVING
   count(*)>1
;


G1
 Briony
G2
 Dan
G3 
 Kim-Joy
 Kim-Joy
G4 
 Manon
G5 
 Rahul
 Rahul
G6 
 Ruby
 Ruby