PostgreSQL Select Sum of Number, Group by Date

Question

I'm not sure if there is question like this out there already, couldn't find my solution, so sorry if this is a duplicate: I have a table with dates:

  |date (date)| tax (numeric) |  (stuff in brackets is the type)
  |2012-12-12 | 5.00          |
  |2012-12-12 | 10.00         |
  |2012-12-13 | 2.00          |

I want my output to look like this:

  |date (date)| tax (numeric) |  (stuff in brackets is the type)
  |2012-12-12 | 15.00         |
  |2012-12-13 | 2.00          |

I was thinking of doing a CTE type query because in the datebase, I store things as datetime without timezone, and a bunch of taxes This is the start of my query:

 with date_select as
 (
 select CAST(r.datetime as DATE), sum(r.tax) as tax
 from suezensalon.receipt r
 where r.datetime between '2012-12-12 00:00:00' and '2012-12-18 23:59:59'
 group by r.datetime  
 order by r.datetime
 )

This gives me the top table. What is the best way to do this? Is it by 'averaging the date'?

In your query you have datetime but in sample data its in date. Is that right? — opensourcegeek
– opensourcegeek, Commented Dec 21, 2012 at 2:11
You should never use BETWEEN when you really want to work with an open or half-open interval, only use BETWEEN with closed intervals. Use separate checks on the end points if your interval isn't closed. — mu is too short
– mu is too short, Commented Dec 21, 2012 at 3:20

akwarywo · Accepted Answer · 2012-12-21 02:37:10Z

1

This is what ended up working:

 with date_select as
 (
 select CAST(r.datetime as DATE), sum(r.tax) as tax
 from suezensalon.receipt r
 where r.datetime between '2012-12-12 00:00:00' and '2012-12-18 23:59:59'
 group by r.datetime  
 order by r.datetime 
 ) 
 select extract(Month from datetime) || '-' || extract(Day from datetime) || '-' ||   extract(Year from datetime) as Date, sum(tax)
 from date_select
 group by Date
order by Date;

answered Dec 21, 2012 at 2:37

akwarywo

1871 gold badge2 silver badges11 bronze badges

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

Comments

kgrittn · Accepted Answer · 2012-12-21 04:18:27Z

0

I think the simplest option, and most likely to perform well in the presence of indexes, would be something like this:

SELECT
    datetime::date as "Date",
    sum(tax)
  FROM suezensalon.receipt
  WHERE datetime >= '2012-12-12 00:00:00'
    AND datetime <  '2012-12-19 00:00:00'
  GROUP BY datetime::date
  ORDER BY "Date";

answered Dec 21, 2012 at 4:18

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Select Sum of Number, Group by Date

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related