CODEWITHSUNDEEP
postgresqldatecommon-table-expression
akwarywo
akwarywo

Reputation: 187

PostgreSQL Select Sum of Number, Group by Date

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'?

Upvotes: 0

Views: 1726

Answers (2)

kgrittn
kgrittn

Reputation: 19511

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";

Upvotes: 0

akwarywo
akwarywo

Reputation: 187

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;

Upvotes: 1

Related Questions