[thelist] MySQL calculations

Michael Pemberton evolt at mpember.net.au
Wed Jun 16 07:20:02 CDT 2004

Good evening all,

I have a MySQL issue that I can't seem to get my head around.

I am attempting to put some of the finer features together on a script I 
use to generated my weekly invoices.  There is no problems with the 
output, just the summary.

I currently have a php script that takes all my outstanding jobs and 
invoices than accordingly.  For each item, it is possible for two types 
of job.  A flat fee that has '0' as the  value for the number of hours, 
and a timed job that is charged based on the number of hours.

Here's what I've got.

SELECT `cust`.`custid` , `invoiceid` , `name` , sum( `amount` )
FROM sales, cust
WHERE `sales`.`custid` = `cust`.`custid` AND (
`paid` IS NULL OR `paid` = '0000-00-00'
GROUP BY `cust`.`custid` , `invoiceid`
ORDER BY `cust`.`custid`

This works fine for when the job is a flat fee, but it won't multiply 
the hours for my timed jobs.  If I change it to this:

SELECT  `cust`.`custid` ,  `invoiceid` ,  `name` , sum(`amount` * 
FROM sales, cust
WHERE  `sales`.`custid`  =  `cust`.`custid`  AND (  `paid`  IS  NULL  OR 
  `paid`  =  '0000-00-00' )
GROUP  BY  `cust`.`custid` ,  `invoiceid`
ORDER  BY  `cust`.`custid`

it multiplies the `amount` value by '0' when there is a flat fee job.

Is there some way of getting mysql to test this value on the fly?  If 
not, am I just gonna have to byte the bullet and resort to calculating 
the correct answer when I generate the page using PHP?

The reason I want to stick with SQL, is because I will then be able to 
dump the output onto my PalmOS database package without any effort.

Thanks for all past and future help.

Michael Pemberton
evolt at mpember.net.au

More information about the thelist mailing list