Chapter 12 SQL Examples - group by and having clauses
------------------------------------
select employee_id,
       last_name,
       first_name,
from l_employees
group by manager_id;

select manager_id
from l_employees
group by manager_id;

select manager_id,
       count (employee_id),
       sum(credit_limit)
from l_employees
group by manager_id;

------------------------------------
p. 439 - Group by clause - sngle column

select manager_id, 
       count(employee_id) as number_of_employees,
       min(credit_limit) as minimum_credit,
       max(credit_limit) as maximum_credit
from l_employees
where not (employee_id = 202)
group by manager_id
order by manager_id;

------------------------------------
p. 442 - Group by multiple columns

select manager_id, 
       dept_code,
       count(employee_id) as number_of_employees,
       min(credit_limit) as minimum_credit,
       max(credit_limit) as maximum_credit
from l_employees
where not (employee_id = 202)
group by manager_id,
         dept_code
order by manager_id,
         dept_code;

------------------------------------
p. 445 - Nulls in Groups

select col_2,
       col_3,
       count(*),
       count(col_4),
       count(col_5)
from sec1204
group by col_2, 
         col_3
order by col_2,
         col_3;

------------------------------------
p. 449 - Cannot mix summary data with non-summary data

select manager_id,
       first_name,
       last_name,
       max(credit_limit)
from l_employees
group by manager_id
order by manager_id;

------------------------------------
p. 451 - Getting around this problem - solution one - add more columns to group by

select manager_id,
       first_name,
       last_name,
       max(credit_limit)
from l_employees
group by manager_id,
         first_name,
         last_name
order by manager_id;

------------------------------------
p. 453 - 454 - Getting around this problem - solution tow - divide into 2 queries

a) Summary

select manager_id,
       max(credit_limit)
from l_employees
group by manager_id
order by manager_id;

b) Detail

select manager_id,
       first_name,
       last_name
from l_employees
order by manager_id;

------------------------------------
p. 456 - subtotals & grand totals - nulls not included

select employee_id,
       count(lunch_id) as number_of_lunches
from l_lunches
group by employee_id
order by employee_id;

------------------------------------
p. 458 - making sure all employees have a total

select employee_id,
       first_name, 
       last_name,
       count(lunch_id) as number_of_lunches
from sec1210
group by employee_id,
         first_name,
         last_name
order by employee_id;

------------------------------------
p. 460 - Having Clause - eliminating some totals

a) no having clause

select supplier_id,
       product_code,
       sum(quantity) as total_servings
from l_lunch_items
group by supplier_id,
         product_code
order by supplier_id,
         product_code;

b) with having clause

select supplier_id,
       product_code,
       sum(quantity) as total_servings
from l_lunch_items
group by supplier_id,
         product_code
having sum(quantity) >= 10
order by supplier_id,
         product_code;

------------------------------------
p. 464 - 465 - Another way to remove data using the where clause instead of the having clause

a) create query with no having clause & save it

select supplier_id,
       product_code,
       sum(quantity) as total_servings
from l_lunch_items
group by supplier_id,
         product_code;

b) create 2nd query based on first & use where clause

select *
from sec1214_view
where total_servings >= 10
order by supplier_id,
         product_code;

------------------------------------
p. 466 - using where clause to eliminate raw data (more efficient)

a) having clause

select manager_id,
       count(*)
from l_employees
group by manager_id
having manager_id between 201 and 203;

b) where clause - more efficient

select manager_id,
       count(*)
from l_employees
where manager_id between 201 and 203
group by manager_id;

------------------------------------
p. 468 - 470 - column function restriction work around

a) problem, cannot have 2 aggregate functions for one column

select supplier_id,
       product_code,
       max(sum(quantity))
from l_lunch_items
group by supplier_id,
         product_code,
         sum(quantity);

b) work around step 1 - create query for one coulumn function & save it

select supplier_id,
       product_code,
       sum(quantity) as total_quantity
from l_lunch_items
group by supplier_id,
         product_code;

c) work around step 2 - apply second column function to saved query

select max(total_quantity)
from sec1216_view;

d) work around step 3 - find row with max value

select supplier_id,
       product_code,
       total_quantity
from sec1216_view
where total_quantity = 17;

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