PLEASE SEE END OF POST FOR UPDATE.
With reference to the table below, I'm looking to write a query which:
1 - Excludes all rows where bool is set to false
2 - Casts time (which is a timestamptz) to date (we'll call this 'D')
3 - Groups by two columns (group and D) and counts the rows
group, time, bool
grp-A, 2020-08-28 13:23:00.000000 +00:00, true
grp-A, 2020-08-28 10:19:00.000000 +00:00, false
grp-A, 2020-08-28 09:23:00.000000 +00:00, true
grp-B, 2020-08-28 13:51:00.000000 +00:00, true
grp-B, 2020-08-27 22:35:00.000000 +00:00, true
grp-B, 2020-08-27 15:10:00.000000 +00:00, true
grp-B, 2020-08-27 22:06:00.000000 +00:00, false
grp-A, 2020-08-27 13:03:00.000000 +00:00, true
The answer should be:
group, time, count(*)
grp-A, 2020-08-28, 2
grp-B, 2020-08-28, 1
grp-A, 2020-08-27, 1
grp-B, 2020-08-27, 2
The query I've tried to use is the following:
select group, time::date D, count(*)
from table
where bool = True
group by group,D;
But it returns the wrong results. The counts are incorrect and it appears to get random/lower numbers.
Any ideas where I'm going wrong with this? Thank you in advance!
IMPORTANT! EDIT: I've isolated this problem to the time::date part of the query. I wrote a new query:
select time::date
from table
When I execute this it returns a list of dates but not all of them - only some! Does anyone know why this could be?
You can try the below -
select grpval, cast(time as date), count(*)
from tablename
where boolval = true
group by grpval, cast(time as date)