mysql SUM

[Anonymous]

i have to tables, 1 store cutomer detail and the 2nd store buy info as follow:

table customer:
name


table buy_info:
buy_id
name
total

Now i have to calculate the grand total of buy_info for each customer.
i try in php code:

$result1=mysql_query("SELECT name FROM customer");
while($row1=mysql_fetch_row($result1)){
$result2=mysql_query("SELECT SUM(total) FROM buy_info WHERE name=$row1['name']");

}

then i will get the list of name with grand total calculated,
but the problem is when my customer are more, my scripts run very slow (more than a minutes) even it works

Is there anything not suitable in my script or how to speed up the script.
could someone help.thanks.

 

[Anonymous]

your customer table should have a primary key usually an int/bigint, auto-incremented.

in your buy_info table you will need that same id contained in the customer table. this is your foreign key. it is easier to have integers as relation than a text name. This will increase speed. next you can do this in one query.

$query = "select sum(buy_info.total) as total, customer.name from customer, buy_info where customer.cust_id = buy_info.cust_id group by buy_info.cust_id order by customer.name asc";

this will return the grand total for each customer.

 

[Anonymous]

Thank you for you advice.
It works more faster than before, but it only show records that total>0.
How could i get records that have total=0

and also if i got 3rd table name buy_info2 which store another buy info.
Now i need a list to show grand total of buy_info + buy_info2


can i try as below:

$query = "select sum(buy_info.total+buy_info2.total) as grand_total, customer.name from customer, buy_info, buy_info2 where customer.cust_id = buy_info.cust_id group by buy_info.cust_id order by customer.name asc";

but seems it doesn't work....any help....