[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