Re: Sum two column selects and put results in a different column

Fri, 23 Jul 2004 09:35:32 -0700

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 = @[EMAIL PROTECTED]
        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, [EMAIL PROTECTED] 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 = @[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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   - [EMAIL PROTECTED]
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/[EMAIL PROTECTED]




 Robert C. Best III     - [EMAIL PROTECTED]
 Computer Coordinator @ Potsdam Central School
 Contact Info At: http://rob.potsdam.k12.ny.us


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to