current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 3 down vote favorite
1

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?

share|improve this question
up vote 2 down vote accepted

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
share|improve this answer
    
That seems to work. And I can reverse it with 'f' to get the opposite. I don't suppose you'd know how to get a list of the indexes which are true? E.g. have it return 1,4,314. – TLP Feb 4 '12 at 23:06
    
@TLP: I think you could do it with a combination of unnest and generate_series; unnest to open up the array and generate_series to provide the index numbers. Then maybe array_agg to put them back together. – mu is too short Feb 4 '12 at 23:17
    
@TLP: Or maybe rank() over to provide the indexes. – mu is too short Feb 4 '12 at 23:25
    
SQL is making my brain hurt. Maybe it's simplest to try to export some array to PHP and do the processing there. If I could use perl, this wouldn't even be a problem. – TLP Feb 4 '12 at 23:41
up vote 1 down vote

You could SUM the array_length function result : 

SELECT SUM(array_length(userconfirm, 2)) WHERE id = 6;
share|improve this answer
1  
I'm sorry, I just realized my question has changed. Arrays do not work as I thought (coming from perl). I am using bool[] instead. Summing the array length does not seem to be the correct solution, as I get 293 as the sum for an array with two non-null values. – TLP Feb 4 '12 at 19:48
up vote 0 down vote

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=#
share|improve this answer
    
This seems awfully complicated. And how would I apply this to my table? I'm not comparing two arrays, I want to compare one array to one value. – TLP Feb 4 '12 at 20:19
    
The unnest function explodes an array into a table structure (the y subquery). On an array converted to a single column table you can use count(*) on a SQL query evaluating your condition. Cheking for res = true, is the same as checking for res. – Alessandro Rossi Feb 4 '12 at 20:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.