# [EMAIL PROTECTED] / 2003-06-16 15:15:31 -0400:
> I have two tables...
> Table 1 newdata contains a lot of statistical data on our customers,
> including last 12months sales amt.
> table 2 sdtik contains all ticket information for all customers.
> Both tables have acctno as primary key.
>
> I can use:
>
> SELECT sum(if(voidreason>0,0,amt)) as sales FROM sdtik where acctno=1234
> and datein>=20020615
>
> to gather customers' sales totals. How can I setup a single query where
> the newdata.sales would be replaced with the summarized data from the
> select.
you need UPDATE or REPLACE, together with GROUP BY
http://www.mysql.com/doc/en/REPLACE.html
http://www.mysql.com/doc/en/UPDATE.html
http://www.mysql.com/doc/en/SELECT.html
REPLACE sales (acctno, sales)
SELECT acctno, SUM(IF(voidreason > 0, 0, amt))
FROM sdtik
GROUP BY acctno
or
UPDATE sales, sdtik
SET sales.sales = SUM(IF(voidreason > 0, 0, sdtik.amt))
WHERE sales.acctno = sdtik.acctno
--
If you cc me or remove the list(s) completely I'll most likely ignore
your message. see http://www.eyrie.org./~eagle/faqs/questions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
