X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by developer.postgresql.org (Postfix) with ESMTP id C5C28924CCB
	for <pgsql-sql@postgresql.org>; Sat, 17 May 2003 12:18:38 -0400 (EDT)
Received: from developer.postgresql.org ([64.117.224.193])
	by localhost (developer.postgresql.org [64.117.224.193:10024])
	(amavisd-new) with ESMTP id 52309-06 for <pgsql-sql@postgresql.org>;
	Sat, 17 May 2003 12:18:37 -0400 (EDT)
Received: from news.hub.org (unknown [64.117.225.234])
	by developer.postgresql.org (Postfix) with ESMTP id 2C4259232B6
	for <pgsql-sql@postgresql.org>; Sat, 17 May 2003 12:18:37 -0400 (EDT)
Received: by news.hub.org (Postfix, from userid 8)
	id F02B8252354; Sat, 17 May 2003 12:17:12 -0400 (EDT)
From: ggunning@esatclear.ie (jmsmithe)
X-Newsgroups: comp.databases.postgresql.sql
Subject: how to use an aggregate function
Date: 17 May 2003 09:18:24 -0700
Organization: http://groups.google.com/
Lines: 103
Message-ID: <de0fa271.0305170818.5972e18e@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Complaints-To: groups-abuse@google.com
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200305/433
X-Sequence-Number: 13598

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),
        FOREIGN KEY(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.