Aggregates/Group By/Having

SQL supports count, sum, avg, max, min functions.

(1) Get total dollar amounts of all orders.

SQL> select sum(dollars) 
     from orders;

SUM(DOLLARS)
------------
        9802

(2) Get total quantity of product "p03" that has been ordered.

SQL> select sum(qty) TOTAL
     from orders 
     where pid = 'p03';

     TOTAL
----------
      2400

(3) Get total number of customers.

SQL> select count(cid)
     from customers;

COUNT(CID)
----------
         5

Alternate solution:
 
SQL> select count(*)
     from customers;

  COUNT(*)
----------
         5

(4) Get number of cities in which customers are based.

SQL> select count(distinct city)
     from customers;

COUNT(DISTINCTCITY)
-------------------
                  3

If distinct keyword was not used, we would get an
incorrect result:

SQL> select count(city)
     from customers;

COUNT(CITY)
-----------
          5

(5) Get cid values of customers who have a discount less than
    the maximum discount.

SQL> select cid 
     from customers
     where discnt < (select max(discnt) 
                     from customers);

CID
----
c001
c003
c004
c006

(6) Get pids of products ordered by at least two customers.

SQL> select p.pid 
     from products p
     where 2 <= (select count(distinct cid) 
                 from orders
                 where pid = p.pid);

PID
---
p01
p03
p05
p07

(7) Null Values and Aggregate Operations:

SQL> insert into customers (cid, cname, city)
     values ('c007', 'Windix', 'Dallas');
1 row created.
SQL> select * 
     from customers 
     where discnt <= 10 or discnt > 10;

CID  CNAME         CITY                     DISCNT
---- ------------- -------------------- ----------
c001 Tiptop        Duluth                       10
c002 Basics        Dallas                       12
c003 Allied        Dallas                        8
c004 ACME          Duluth                        8
c006 ACME          Kyoto                         0

Newly created row not in result!

SQL> select * 
     from customers 
     where discnt is null;

CID  CNAME         CITY                     DISCNT
---- ------------- -------------------- ----------
c007 Windix        Dallas

Newly created row is in result.

All aggregate functions discard null values before evaluation.

Get average discount value for customers.

SQL> select avg(discnt) 
     from customers;

AVG(DISCNT)
-----------
        7.6

This discarded the null value before calculating average.