Reputation: 187
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
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
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