2
\$\begingroup\$

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;
\$\endgroup\$

1 Answer 1

2
\$\begingroup\$

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.