how to use an aggregate function - Mailing list pgsql-sql

Hello

Imagine if you would if I had a database like this:

DROP TABLE Customer;
CREATE TABLE Customer(       CustomerID int,       Cname varchar(10),       City varchar(10),       PRIMARY
KEY(CustomerID)
);

DROP TABLE Item;
CREATE TABLE Item(       ItemID int,       UnitPrice int,       PRIMARY KEY(ItemID)
);

DROP TABLE AOrder;
CREATE TABLE AOrder(       AOrderID int,       Odate date,       CustomerID int,       Ird_Amt int,       PRIMARY
KEY(AOrderID),      FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID)
 
);

DROP TABLE OrderItem;
CREATE TABLE OrderItem(       AOrderID int,       ItemID int,       Qty int,       PRIMARY KEY(AOrderID, ItemID),
FOREIGNKEY(AOrderID) REFERENCES AOrder(AOrderID),       FOREIGN KEY(ItemID) REFERENCES Item(ItemID)
 
);

DROP TABLE Warehouse;
CREATE TABLE Warehouse(       WarehouseID int,       Wcity varchar(10),       PRIMARY KEY(WarehouseID)
);

DROP TABLE Shipment;
CREATE TABLE Shipment(       AOrderID int,       WarehouseID int,       ShipDate date,       PRIMARY KEY(AOrderID,
WarehouseID),      FOREIGN KEY(AOrderID) REFERENCES AOrder(AOrderID),       FOREIGN KEY(WarehouseID) REFERENCES
Warehouse(WarehouseID)
);


INSERT INTO Customer VALUES(1, 'Jack', 'NeverLd');
INSERT INTO Customer VALUES(2, 'Jill', 'Wall');
INSERT INTO Customer VALUES(3, 'Emma', 'Desolat');

INSERT INTO AOrder VALUES(1, '1/1/2003', 1, 3);
INSERT INTO AOrder VALUES(2, '1/1/2003', 3, 3);

INSERT INTO Item VALUES(1, 5);
INSERT INTO Item VALUES(2, 5);
INSERT INTO Item VALUES(3, 15);

INSERT INTO OrderItem VALUES(1, 1, 1);
INSERT INTO OrderItem VALUES(1, 2, 5);
INSERT INTO OrderItem VALUES(1, 3, 15);

INSERT INTO OrderItem VALUES(2, 1, 2);
INSERT INTO OrderItem VALUES(2, 2, 5);

INSERT INTO Warehouse VALUES(1, 'Water fall');
INSERT INTO Warehouse VALUES(2, 'Fall vill');

INSERT INTO Shipment VALUES(1, 2, '1/1/2002');
INSERT INTO Shipment VALUES(2, 1, '1/1/2002');


How would I
List the name of customers who have paid an average of more then $6.
Produce a listing Cname, Avg_Paid

All I can think of is this.

SELECT Customer.Cname, AVG(Item.UnitPrice) AS Avg_Paid
FROM Customer
JOIN AOrder
ON Customer.CustomerID = AOrder.CustomerID
JOIN OrderItem
ON AOrder.AOrderID = OrderItem.AOrderID
JOIN Item
ON OrderItem.ItemID = Item.ItemID
GROUP BY Customer.Cname
;

I can't figure out how to limit it to over $6?


Thanks for any help.