Re: divide 10 by 3

How about:

select	c.location_id,
	p.person_id,
	truncate(c.cost / c.number_of_people,2) +
	decode( sign( 100*(c.cost-c.number_of_people
				*truncate(c.cost / c.number_of_people,2))
			- rownum),
		-1, 0, 0.01 )

from	costs c,
	people p

where	p.location_id = c.location_id
and	p.product = c.product

Basically this is supposed to figure out how many pennies are left after rounding down, and then add one penny for the first N people. The syntax truncate(x,2) is meant to be whatever expression truncates a number to two decimal places; I can't remember what function to use or what its parameters are (and I haven't access to a db or manual). You may actually have to multiply by 100 to do it right.

Note that if you don't have the c.number_of_people stored, you could calculate it in a subquery or by joining and grouping. Received on Tue Mar 01 1994 - 22:26:42 CET