Group By/ Having clauses

select [distinct] expression {, expression}
from tablename [corr_name] {, tablename [corr_name]}
[where search_condition]
[group by column {, column}]
[having search_condition]

• Group By clause is used to form groups of rows of a resulting table based on column values. Aggregate operations work on groups and not whole table.
• Having clause is used to eliminate certain groups from further considerations.

(1) For each (aid,pid) pair get the sum of the orders
    aid has placed for pid.

SQL> select pid, aid, sum(qty) TOTAL 
     from orders
     group by pid, aid;

PID AID      TOTAL
--- --- ----------
p01 a01       3000
p01 a06       1800
p02 a02        400
p03 a03       1000
p03 a05        800
...
...

12 rows selected.

(2) Get agent name, agent id, product name, product id, 
    together with total quantity each agent supplies of 
    that product to customers "c002" and "c003".

SQL> select  aname, a.aid, pname,  p.pid, sum(qty)
     from orders x, products p, agents a
     where x.pid = p.pid and x.aid = a.aid and 
           x.cid in ('c002', 'c003')
     group by a.aid, a.aname, p.pid, p.pname;

ANAME         AID PNAME         PID   SUM(QTY)
------------- --- ------------- --- ----------
Brown         a03 razor         p03       1000
Otasi         a05 razor         p03        800
Brown         a03 pencil        p05       2400

Note: The where clause is evaluated before groups are formed.

(3) Get product ids and total quantity ordered for each 
    product when the total exceeds 1000.

SQL> select pid, aid, sum(qty) TOTAL 
     from orders
     group by pid, aid
     having sum(qty) > 1000;

PID AID      TOTAL
--- --- ----------
p01 a01       3000
p01 a06       1800
p05 a03       2400

(4) Get pids of products that are ordered by at least two
    customers.

SQL> select pid from orders
     group by pid
     having count(distinct cid) >= 2;

PID
---
p01
p03
p05
p07