Thread: how to use an aggregate function

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.

Hi, I'm not an expert, but I believe I had this same problem recently.  I
think that what you need to do is use the "HAVING" phrase at the end.  You
can use it instead of or in addition to a where clause.

For a simple example, 

SELECT * FORM table HAVING Avg(price) > 6;

This should get you past the error about using an aggregate in the where
clause.

--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of jmsmithe
> Sent: Saturday, May 17, 2003 12:18 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] how to use an aggregate function
> 
> 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.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)