improperly constructed join for sum()

[JJSRich]

I have written the following to tell me how much a particular quote_id is worth, given a set of circumstances. I don't know if my nested ifs would be better written as a case, but that may or may not solve the problems I am facing. The hoursworked table tracks when, and how long people worked on a particular housetype. there are multiple housetypes in a quote_id.

select
q.quote_id
,q.date_rtn_final
,q.add1
,r.rate_name
,if(q.rate_id = 2,q.fix_price,
   if(r.hourly=1,(sum(w.hoursworked) * if(q.hrate='0.00',31.25,q.hrate)),
    if(q.rate_id = 1,0,
    (sum(h.hours) * if(q.hrate='0.00',31.25,q.hrate))
    )
   )
  ) as value
from quotations as q
inner join housetype as h on h.quote_id = q.quote_id and h.deleted = 0
inner join hoursworked as w on h.ht_id = w.ht_id
inner join rates as r on q.rate_id = r.rate_id
group by q.quote_id

Where I seem to be stumbling is, if the hoursworked table has multiple hours logged for one housetype (possible, since work can be spread out across multiple days), it throws off the sum(h.hours) calculation. The sum(h.hours) calculation should return the number of hours assigned in the housetype table. When I broke it down (removed the If() block and the group by) I got duplicate records when there was more than one entry in the hoursworked table. What am I doing wrong?

If it needs more explanation, please let me know, heck I'm confused and I designed the database (Maybe not as well as I could have).

 

[JJSRich]

Nevermind, solved it myself. Here is the code now, pruned and turned into a view for joining. Also may be useful for anyone else dealing with aggregates.

select
q.quote_id
,if(q.rate_id = 2,q.fix_price,
   if(r.hourly=1,(
    (select sum(w.hoursworked) 
	 from hoursworked as w 
	 left join housetype as h on w.ht_id = h.ht_id and h.deleted = 0
	 where h.ht_id = q.quote_id
	) * if(q.hrate='0.00',31.25,q.hrate)),
    if(q.rate_id = 1,0,(
	 (select sum(h.hours) 
	  from housetype as h 
	  where h.quote_id = q.quote_id and h.deleted = 0
	 ) * if(q.hrate='0.00',31.25,q.hrate))
    )
   )
  ) as value
from quotations as q
inner join rates as r on q.rate_id = r.rate_id
group by q.quote_id

Couldn't do it because of how I was aggregating things, as well as a few other gotchas I wasn't aware of. I'm off to re-write some code now.