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

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)