PostgreSQL query with sum and condition

Question

I have the following simplified postgresql table 'fees':

          Table "public.fees"
id |           datetime          | fee         
---+-----------------------------+----
1  |  2013-10-17 09:11:00.138021 | 5
2  |  2013-10-17 09:15:02.848841 | 20

4  |  2013-10-18 09:17:40.784396 | 40
5  |  2013-10-18 09:29:21.789913 | 10
16 |  2013-10-18 09:39:38.308201 | 10

17 |  2013-10-19 09:40:09.507662 | 10
18 |  2013-10-19 09:40:14.310772 | 10

22 |  2013-10-20 09:54:37.183343 | 40

35 |  2013-10-21 10:32:28.619779 | 10
39 |  2013-10-21 10:34:50.830838 | 10

I want a sql result like this:

|   datetime   | feeSum                  
---------------+----
|  2013-10-17  | 25    
|  2013-10-18  | 60    
|  2013-10-19  | 20   
|  2013-10-20  | 40    
|  2013-10-21  | 20

which sums each day's fee to feeSum.

I've searched a lot and try this:

create view temp as
select datetime, fee  from fees
where
datetime::date>='2013-10-17' and
datetime::date<='2013-10-21';


do $$
    declare
        fromdate date := '2013-10-17';
        todate date := '2013-10-21';
    begin
        for d in fromdate..todate
        loop
            select sum(fee) as feeSum, d as datetime from temp
            where
            datetime::date=d;
        end loop;
    end;
$$;

but got error:

ERROR:  invalid input syntax for integer: "2013-10-17"

and I can't find how to define date variable. Or is there another way to get my result?

I would use the date_trunc function date_trunc('day', datetime) as date. — Elliott Frisch
– Elliott Frisch, Commented Dec 7, 2013 at 4:05
@ElliottFrisch where datetime::date>='2013-10-17' and datetime::date<='2013-10-21'; no error. fromdate date := '2013-10-17'; here cause error — Aylwyn Lake
– Aylwyn Lake, Commented Dec 7, 2013 at 4:40

gwaigh · Accepted Answer · 2013-12-07 17:53:07Z

3

I generally write date constants with a date cast:

DATE'2013-10-17' or '2013-10-17'::date

Which will address your syntax error.

As for your query, you can eliminate the procedure and query it directly:

  SELECT datetime::date AS datetime,
         SUM(fee) AS feeSum
    FROM fees
   WHERE datetime::date BETWEEN '2013-10-17' AND '2013-10-21'
GROUP BY datetime::date

As people pointed out in comments, Postgres will do an implicit cast in the WHERE clause to a date type. I tend to make the cast explicit, but I am not sure which systems would require it.

edited Dec 7, 2013 at 17:53

answered Dec 7, 2013 at 4:13

gwaigh

1,3421 gold badge13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mosty Mostacho Over a year ago

I'm a bit ignorant about PosgreSQL so I have a couple of questions. 1) Would it be the same in terms of performance to use date(datetime) instead of the casting? 2) Do you need to cast the string in the where clause or will the parser infer it as a date? 3) In case you don't need the previous cast would it be the same in terms of performance? 4) If you had written datetime::date AS blah and then grouped by blah would it have been faster?

Aylwyn Lake Over a year ago

Yeah. Your directly query works good. For your question: 2)yes, I need to use datetime::date to cast timestamp to date; 4)I don't know, I'll try later.

Aylwyn Lake Over a year ago

In where clause, postgres don't need to cast '2013-10-17' to '2013-10-17'::date. While, in declare clause, even you cast '2013-10-17', error still there. I've made a mistake, the reason why I cast datetime to datetime::date is at the begining I made an equal statement. If use between, there is no need to cast datetime. Sorry for confusing.

Collectives™ on Stack Overflow

PostgreSQL query with sum and condition

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related