Re: how to use an aggregate function

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(at)bearfruit(dot)org

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-
> owner(at)postgresql(dot)org] On Behalf Of jmsmithe
> Sent: Saturday, May 17, 2003 12:18 PM
> To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org)