pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to use an aggregate function
3+ messages / 3 participants
[nested] [flat]
* how to use an aggregate function
@ 2003-05-17 16:18 jmsmithe <ggunning@esatclear.ie>
0 siblings, 2 replies; 3+ messages in thread
From: ggunning@esatclear.ie @ 2003-05-17 16:18 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to use an aggregate function
@ 2003-05-17 17:14 David W Noon <dwnoon@spamtrap.ntlworld.com>
parent: jmsmithe <ggunning@esatclear.ie>
1 sibling, 0 replies; 3+ messages in thread
From: David W Noon @ 2003-05-17 17:14 UTC (permalink / raw)
To: pgsql-sql
On Saturday 17 May 2003 17:18 in
<de0fa271.0305170818.5972e18e@posting.google.com>, jmsmithe
(ggunning@esatclear.ie) wrote:
[snip]
> How would I
> List the name of customers who have paid an average of more then $6.
> Produce a listing Cname, Avg_Paid
In your Item table, UnitPrice is an INT. Please choose a better data type
for monetary values, as an average is problematic in an integer domain: the
average is usually not an element of that domain. Something like
DECIMAL(6,2) could be good.
> 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
HAVING Avg_paid > 6.00
> ;
Try this after changing the data type on Item.UnitPrice.
--
Regards,
Dave
======================================================
dwnoon@spamtrap.ntlworld.com (David W Noon)
Remove spam trap to reply via e-mail.
RLU#314465
======================================================
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to use an aggregate function
@ 2003-05-23 01:14 Matthew Nuzum <cobalt@bearfruit.org>
parent: jmsmithe <ggunning@esatclear.ie>
1 sibling, 0 replies; 3+ messages in thread
From: Matthew Nuzum @ 2003-05-23 01:14 UTC (permalink / raw)
To: 'jmsmithe' <ggunning@esatclear.ie>; +Cc: 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)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-05-23 01:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-17 16:18 how to use an aggregate function jmsmithe <ggunning@esatclear.ie>
2003-05-17 17:14 ` David W Noon <dwnoon@spamtrap.ntlworld.com>
2003-05-23 01:14 ` Matthew Nuzum <cobalt@bearfruit.org>
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