MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Rob Best Date:July 23 2004 4:30pm
Subject:Re: Sum two column selects and put results in a different column
View as plain text  
Thanks! that worked great!
I ended up using the first one, I just liked the look of it better.
Because the customer might not have any deposit records or any 
purchases I changed the code to this:

SELECT @purchases := SUM(amount_of_credit_used)
	FROM purchases WHERE customer_id="jdoe";
	
SELECT @deposits := SUM(amount)
	FROM deposits WHERE customer_id="jdoe";

SELECT @purchases :=IFNULL(@purchases, 0);

SELECT @deposits :=IFNULL(@deposits, 0);

UPDATE customers
	SET balance_cache = @purchases+@deposits
	WHERE customer_ID = "jdoe";


Notice the "IFNULL" lines.

Thanks a million!!

I would nice to be able to run the above sql statement for all 
customers in the 'customers' table but I found a work around (a big 
.sql file that runs every night).

Something like:
SELECT @customers := customer_id from customers;
foreach @customers
{
	SELECT @purchases := SUM(amount_of_credit_used)
		FROM purchases WHERE customer_id="@customers";
		...
		...
}

But hey, I've got it working at least (thanks again!)

On Jul 23, 2004, at 9:05 AM, SGreen@stripped wrote:

> I know there are more ways to solve this problem (I can think of at 
> least
> 2 more) but I think the easiest approach is to break this into two 
> stages.
> First stage, we compute the sums of the purchases and the credits:
>
> SELECT @purchases := SUM(amount_of_credit_used)
> FROM purchases
> WHERE customer_id='jdoe';
>
> SELECT @deposits := SUM(Amount)
> FROM deposits
> WHERE customer_id='jdoe'
>
> Then we can do the update to the customers table
>
> UPDATE customers
> SET balance = @purchases+@deposits
> WHERE customer_ID = 'jdoe';
>
> A second method is to perform two UPDATES in sequence:
>
> UPDATE customers c
> INNER JOIN purchases p
>         on c.customer_ID = p.customer_ID
> SET c.balance = SUM(p.amount_of_credit_used)
> WHERE c.customer_ID = 'jdoe';
>
> UPDATE customers c
> INNER JOIN deposits d
>         on c.customer_ID = d.customer_ID
> SET c.balance = c.balance + SUM(d.amount)
> WHERE c.customer_ID = 'jdoe';
>
> The first update replaces the old value in customers.balance with the
> "credit used" total while the second update builds on the first value.
>
> Do either of these approaches make sense for you?
>
> I don't know how busy your database is but for a real-world 
> application I
> would either lock the tables to prevent outside updates during the
> computation, or I would wrap the whole process with a transaction. That
> way you can avoid updating the balance with only part of the 
> information.
>
> Yours,
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> Rob Best <rbest@stripped> wrote on 07/23/2004 07:24:02 AM:
>
>> I hope this isn't a stupid question but this is my best place to 
>> ask...
>>
>> I have a database where I need to do a sum of a returned select...
>>
>> SELECT SUM(purchases.amount_of_credit_used) WHERE customer_id="jdoe";
>>
>> and do a sum of another returned select...
>>
>> SELECT SUM(amount) from deposits WHERE customer_id="jdoe";
>>
>>
>> So far so good. Above two statements work fine.
>> The problem is I need to two sums added together and put into a third
>> table/field (customers.balance).
>> shorthand I would have expected the sql statement to look something
>> like...
>>
>> UPDATE customers SET balance=(
>>    SUM(
>>          SELECT SUM(purchases.amount_of_credit_used) WHERE
> customer_id="jdoe",
>>          SELECT SUM(amount) from deposits WHERE customer_id="jdoe"
>>    )
>> ) WHERE customer_id="jdoe".
>>
>>
>> Unfortunately it does not work.
>> Does anyone know if what I want is possible? If so, would you be so
>> kind as to provide sample sql statement?
>> Thanks!
>>
>>
>>   Robert C. Best III   - rbest@stripped
>> District Technology Coordinator
>>   for N.E.R.I.C. at Potsdam Central School
>>   Phone: (315) 265-2000 x266
>>
>>
>> -- 
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    
>> http://lists.mysql.com/mysql?unsub=1
>>
>>


  Robert C. Best III	- rbest@stripped
  Computer Coordinator @ Potsdam Central School
  Contact Info At: http://rob.potsdam.k12.ny.us

Thread
Sum two column selects and put results in a different columnRob Best23 Jul
  • Re: Sum two column selects and put results in a different columnSGreen23 Jul
    • grants for non-rootWolfgang Riedel23 Jul
      • Re: grants for non-rootAman Raheja23 Jul
    • Re: Sum two column selects and put results in a different columnRob Best23 Jul