From: ggunning@esatclear.ie
To: pgsql-sql@postgresql.org
Subject: how to use an aggregate function
Date: 17 May 2003 09:18:24 -0700
Message-ID: <de0fa271.0305170818.5972e18e@posting.google.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ggunning@esatclear.ie
Subject: Re: how to use an aggregate function
In-Reply-To: <de0fa271.0305170818.5972e18e@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox