[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` * 
`quantity`)
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