[thelist] MySQL calculations

Michael Pemberton evolt at mpember.net.au
Wed Jun 16 09:43:07 CDT 2004


Tab Alleman wrote:

> http://dev.mysql.com/doc/mysql/en/CASE_Statement.html

Thanks.  I knew there was something that would bring it all together.

For those who are interested, here's the result:

SELECT `cust`.`custid` , `invoiceid` , `name` , sum( CASE
WHEN `quantity` = 0 THEN `amount`
WHEN `quantity` IS NULL THEN `amount`
ELSE `amount` * `quantity`
END ) as outstanding
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`

-- 
Michael Pemberton
evolt at mpember.net.au




More information about the thelist mailing list