Summing different tables values limiting the results

Question

I have two tables (a and b) which have both one date field of type TIMESTAMP. They represent two different type of actions (records): table a's actions are 200ドル worth each, while table b's ones worth 500ドル.

I want to calculate the total count of dollars worth of both tables actions limiting the maximum number or dollars per day to 10ドル.000. Is there a more clean or efficient way to do this other than:

SELECT SUM(daily_sum_total) AS total
FROM (
 SELECT CASE WHEN SUM(daily_sum) > 10000 THEN 10000 ELSE SUM(daily_sum) END AS daily_sum_total, day
 FROM (
 SELECT COUNT(date) * 200 AS daily_sum, DATE(date) AS day
 FROM a 
 GROUP BY day
 UNION ALL
 SELECT COUNT(date) * 500 AS daily_sum, DATE(date) AS day
 FROM b
 GROUP BY day
 ) AS daily_tmp_table
 GROUP BY day
) as total_tmp_table;

Answer 1

You might try this:

select
 sum(amount) amount
from(
 select
 least(sum(amount),10000) amount
 from (
 select date,
 200 amount
 from a
 union all
 select date,
 500 amount
 from b) list_of_all
 group by
 date(date)) list_by_day

• sh03: What's the difference between date(date) and date_trunc('day', date)? Also your current form does not sum all the returned results.
• David Aldridge: Never used Date actually -- I see it does a type casting. If you want a date data type I guess that either date() or cast() make sense. cast() is the ANSI version I think. I believe this does sum all the results as the sum runs against an inline view that projects all the rows of a & b.
• sh03: "I believe this does sum all the results as the sum runs against an inline view that projects all the rows of a & b" - I've just run your query and all it does is returning a column of rows of the amounts grouped by date. You have to put run a SELECT sum(amount) AS rep FROM x; (where x is your current query) to get the total amount. Also when using a subquery in the FROM clause you should specify an alias.
• David Aldridge: Ah, when I changed the select to a Date() instead of a date_trunc() I forgot to change the group by ... perhaps that was it. Yes, I'll add the alias -- keep forgetting that PostgreSQL requires it.
• sh03: syntax error at or near "day", LINE 1: SELECT date(date) day. (you should always use AS when aliasing). The error is probably caused by the fact that day is also a function. Also even with that fix it is still showing only the rows column of all amounts, not the actual sum().