What I would like is to count the array elements which corresponds to true (attendance), false (non-attendance) and NULL for any single event.
EDIT:
I just realized that arrays do not behave as I thought in pSQL, so a simple
userconfirm bool[]
Might suffice. However, I am still having the same problem counting true/false/null values. I will attempt to edit the question below to match this new constraint. I apologize for any errors.
I have a column such as
userconfirm bool[]
Where userconfirm[314] = true
would mean that user #314 will attend. (false = no attend, NULL = not read/etc).
I'm not sure this is the best solution for this functionality (users announce their attendance to an event), but I am having trouble with an aggregate function on this column.
select count(*) from foo where id = 6 AND true = ANY (userconfirm);
This only returns 1, and trying to google "counting arrays" does not turn up anything useful.
How would I go about counting the different values for a single event?
You can use unnest
in your SELECT like this:
select whatever,
(select sum(case b when 't' then 1 else 0 end) from unnest(userconfirm) as dt(b))
from your_table
-- ...
For example, given this:
=> select * from bools;
id | bits
----+--------------
1 | {t,t,f}
2 | {t,f}
3 | {f,f}
4 | {t,t,t}
5 | {f,t,t,NULL}
You'd get this:
=> select id, (select sum(case b when 't' then 1 else 0 end) from unnest(bits) as dt(b)) as trues from bools;
id | trues
----+-------
1 | 2
2 | 1
3 | 0
4 | 3
5 | 2
If that's too ugly, you could write a function:
create function count_trues_in(boolean[]) returns bigint as $$
select sum(case b when 't' then 1 else 0 end)
from unnest($1) as dt(b)
$$ language sql;
and use it to pretty up your query:
=> select id, count_trues_in(bits) as trues from bools;
id | trues
----+-------
1 | 2
2 | 1
3 | 0
4 | 3
5 | 2
You could SUM the array_length function result :
SELECT SUM(array_length(userconfirm, 2)) WHERE id = 6;
This one may do the trick(unnest).
postgres=# with y(res) as (
postgres(# with x(a) as (
postgres(# values (ARRAY[true,true,false])
postgres(# union all
postgres(# values (ARRAY[true,null,false])
postgres(# )
postgres(# select unnest(a) as res
postgres(# from x
postgres(# )
postgres-# select count(*)
postgres-# from y
postgres-# where res;
count
-------
3
(1 row)
postgres=#
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With