[SOLVED] Sums in update query not working properly

[anser316]

Hi I have a problem with my sql query

$result5 =mysql_query("UPDATE prescription p, prescription_items i
SET p.quantity_total=(SELECT SUM(i.drug_quantity)),
p.presc_total=(SELECT SUM(i.drug_total)),
cust_total='0',
nhs_total=(SELECT SUM(i.drug_total))
WHERE p.presc_id=$_POST[h_prescid]
AND p.presc_id=i.presc_id");

 

For the sums, it only displays the first rows figures, instead of adding them all and putting the total

 

e.g.

Presc_Items

PID    DID  Quantity    Total

35        1        2            10

35        2        3            12

 

Presc

PID      TQuantity    PTotal  NHSTotal

35            2            10          10

 

It should be:

Presc

PID      TQuantity    PTotal  NHSTotal

35            5            22          22

[fenway]

You can't select sum() like that.

[anser316]

so how do you?

[gluck]

Can you post the table structure? Also what is the desired result?

[anser316]

A prescription has many items.

e.g.This is what I get

Presc_Items - This works fine

item_no presc_id  d_id  quantity  drug_total £

26   37         2 3   30.00

27   37         4 5   25.00

 

 

This is where I get my problem, with quantity total, presc_total nhs_total. It seems to only take the first row figures where i ask to have the sum of all data. I made an earlier insert, which inserted date and branch.

presc_id          date            quant_total  presc_total  exemption  cus_total  nhs_total  branch_id

37   2008-04-10 00:57:56 3           30.00        YES          0.00        30.00        1

 

It should be:

Presc

presc_id          date            quant_total  presc_total  exemption  cus_total  nhs_total  branch_id

37   2008-04-10 00:57:56     8          55.00          YES        0.00        55.00        1

 

create table prescription(
presc_id	SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
presc_date	DATETIME not null,
quantity_total	FLOAT NOT NULL,
presc_total	DECIMAL(5,2) not null,
exemption_type	ENUM('NO','YES') NOT NULL,
cust_total	DECIMAL(5,2) not null,
nhs_total	DECIMAL(5,2) not null,
branch_id	TINYINT(2) UNSIGNED NOT NULL,
CONSTRAINT prescription_prid_pk primary key (presc_id),
CONSTRAINT prescription_ext_fk foreign key (exemption_type)
references	exemption(exemption_type),
CONSTRAINT prescription_bid_fk foreign key (branch_id)
references	branch(branch_id));

 

create table prescription_items (
item_no		tinyint(2) unsigned not null AUTO_INCREMENT,
presc_id	smallint(4) unsigned not null,
drug_id		smallint(4) unsigned not null,
presc_dosage	varchar(10) not null,
drug_quantity	int unsigned not null,
drug_total	decimal(10,2) not null,
CONSTRAINT prescitems_ino_pk primary key (item_no),
CONSTRAINT prescitems_pid_fk foreign key (presc_id)
references	prescription(presc_id),
CONSTRAINT prescitems_did_fk foreign key (drug_id)
references	drugs(drug_id));

[fenway]

Why would you store the totals like that? And if you really wanted to, you'd need to run proper subqueries.