# Summing different tables values limiting the results

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;


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

• What's the difference between date(date) and date_trunc('day', date)? Also your current form does not sum all the returned results.
– Shoe
Jun 1, 2013 at 16:13
• 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. Jun 1, 2013 at 16:53
• "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.
– Shoe
Jun 1, 2013 at 17:40
• 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. Jun 1, 2013 at 18:21
• 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().
– Shoe
Jun 1, 2013 at 18:26