How do I add all data in a column and limit or cap each row's amount?

[vedub4us]

I have a table called jobs that has a total column which contains integers between 0 and 25. I have a deal worked out with a client that caps the total per job at 9. How do I add multiple days to get the months total while capping any day whose total is over 9. I have tried looping it in php which I can't get to work, seems like it should be part of the query structure anyway. Thanks for your time.

Tim

[joelgreen]

I think something like this

select jobname,sum(total)
from tablename
where total > 9
group by total

[vedub4us]

I haven't had time to try it yet, but wont that query only add results that are greater than 9? I need it to be more like a LIMIT but I don't think LIMIT works that way? or does it?

Just to clarify: If I had 4 days last month whose totals were 4, 6, 10 and 17. I would need 10 and 17 to limit at 9 and the total = 28 not 37.

[joelgreen]

did not try it, but think it is what you need. Made a mistake in the query, should have been grouping by month

select jobname, sum(total)
from tablename
where total > 9
group by month

[vedub4us]

Ok I tried it and it only returned results greater than 9. I know how to do the rest of the query but I still can't figure out how to limit each row to 9. Thanks for your time.

[vedub4us]

Ok I found it, here it is:

SELECT SUM(CASE WHEN total>9 THEN 9 ELSE Total END) AS total FROM jobs;