SELECT Person.firstName, Person.lastName, c.companyName, c.dueDate, c.amount, c.amount/COUNT(c.dueDate) AS amountOwed
FROM Person,
(SELECT *
FROM Statement, Bill
WHERE Statement.bill_id = Bill.id AND Statement.dueDate >= '05-Dec-06' AND Bill.billingAddress_id = 101) c
WHERE Person.address_id = c.billingAddress_id
GROUP BY dueDate, firstName, lastName, companyName, dueDate, amount;
Result:
FIRSTNAME LASTNAME COMPANYNAME DUEDATE AMOUNT AMOUNTOWED
-------------------------------- -------------------------------- ---------------------------------------------------------------- --------- ---------- ----------
Alan Ableton Federal Electric 18-DEC-06 202.77 202.77
Bill Brown Federal Electric 18-DEC-06 202.77 202.77
Clark Cambell Federal Electric 18-DEC-06 202.77 202.77
David Dotts Federal Electric 18-DEC-06 202.77 202.77
Alan Ableton Time Warner Cable 25-DEC-06 99.98 99.98
Bill Brown Time Warner Cable 25-DEC-06 99.98 99.98
Clark Cambell Time Warner Cable 25-DEC-06 99.98 99.98
David Dotts Time Warner Cable 25-DEC-06 99.98 99.98