Trouble with an aggregate function in a query.

From: Bob Billingsley <wwroller5_at_yahoo.com>
Date: 22 Mar 2002 19:20:24 GMT
Message-ID: <1016824824.461915@proxy.storm.co.za>

Hi, everyone. I am not an SQL expert by trade, and I am having trouble getting a partiular simple query to go my way. The aggregate functions (sums) are not coming out correctly as I expect.

It would be impossible to describe, so I will post a script here that sets up the 5 tables involved, and runs the query, so you can look at it for yourself.

Thanks so much to anyone who can suggest a better way to approach this.

If it's easier, my email is wwroller65_at_yahoo.com.

(script included below this line:)

/*Create the 5 test tables*/

create table t_clients (client_id number(2), name varchar2(20), primary key(client_id));

create table t_orders (order_id number(2), client_id number(2), primary key(order_id), constraint temporary_fk1 foreign key(client_id) references t_clients(client_id));

create table t_products (product_id number(2), sale_price number(5), primary key(product_id));

create table t_order_details (order_id number(2), product_id number(2), quantity number(2), primary key(order_id,product_id), constraint temporary_fk2 foreign key(order_id) references t_orders(order_id), constraint temporary_fk3 foreign key(product_id) references t_products(product_id));

create table t_payments_received (payment_id number(2), client_id number(2), amount number(5), primary key(payment_id), constraint temporary_fk4 foreign key(client_id) references t_clients(client_id));

/*Load table t_clients*/

insert into t_clients values (4, 'Mos Eisley');
insert into t_clients values (5, 'The Empire');
insert into t_clients values (7, 'The Jedi Counsel');
insert into t_clients values (10, 'Yoda, Inc.');
insert into t_clients values (3, 'Hoth');
insert into t_clients values (9, 'Spice Mines');

/*Load table t_products*/

insert into t_products values (1, 300);
insert into t_products values (2, 20000);
insert into t_products values (3, 1500);
insert into t_products values (4, 45);
insert into t_products values (5, 34000);

/*Load table t_orders*/

insert into t_orders values (6,7);
insert into t_orders values (7,5);
insert into t_orders values (8,5);
insert into t_orders values (9,7);
insert into t_orders values (10,10);
insert into t_orders values (11,10);
insert into t_orders values (12,4);

/*Load table t_order_details*/

insert into t_order_details values (6,3,72);
insert into t_order_details values (7,2,1);
insert into t_order_details values (7,4,7);
insert into t_order_details values (8,1,1);
insert into t_order_details values (9,1,25);
insert into t_order_details values (9,5,4);
insert into t_order_details values (10,4,8);
insert into t_order_details values (11,3,2);
insert into t_order_details values (12,3,3);
insert into t_order_details values (12,4,2);

/*Load table t_payments_received*/

insert into t_payments_received values (1,5,4500);
insert into t_payments_received values (2,7,500);
insert into t_payments_received values (3,10,500);
insert into t_payments_received values (5,5,750);

/*this is the query that has me stumped. You can see that the "total
bought" column and the "total paid" column are not correct, because the calculations are counting payments and order details multiple times. (for example, The Empire has only made 2 payments, totaling $5250, but the "amount paid" column in this query shows exactly 3 times that number ($15750). Please see if you can suggest a query that will give the desired correct reslts.*/

SELECT c.client_id, c.name, sum(p.sale_price*od.quantity) "total bought", sum(nvl(pr.amount,0)) "total paid"
FROM t_clients c, t_orders o, t_order_details od, t_products p, t_payments_received pr
WHERE o.client_id = c.client_id
AND pr.client_id(+) = c.client_id
AND od.order_id = o.order_id
AND p.product_id = od.product_id
GROUP BY c.client_id, c.name;

/*Thanks so much*/

Posted via www.orafocus.com - Focusing on the World of Oracle Received on Fri Mar 22 2002 - 13:20:24 CST