SQL counting unique true values on join table

Question

I have a users table

Table "public.users"
Column  |  Type   | Modifiers 
--------+---------+-----------
user_id | integer | 
style   | boolean | 
id      | integer |

and an access_rights table

Table "public.access_rights"
Column  |  Type   | Modifiers 
--------+---------+-----------
user_id | integer | 
id      | integer |

I have a query joining users on access right and I want to count the number of values in the style column that are true.

From this answer: postgresql - sql - count of `true` values, I tried doing

SELECT COUNT( CASE WHEN style THEN 1 ELSE null END )
from users
join access_rights on access_rights.user_id = users.user_id
;

But that counts duplicate values when a user has multiple rows for access_rights. How can I count values only once when using a join?

why don't you first filter by the value and then count the rows? you will need to add a where clause to your query `where style = 1 OR NULL`, that will return only rows with style value equal to true and then you can count the rows. — AR5HAM, Jul 01 '14 at 19:25
if you have two records with `user_id = 1, style = TRUE` in users and three records with `user_id = 1` in access_rights, what do you expect to return? 1, 2, 3, or 6? — Quassnoi, Jul 01 '14 at 19:32
This is a contrived example but the user_id column can be considered a primary key so that wouldn't happen — jvans, Jul 01 '14 at 21:04

score 1 · Accepted Answer · edited May 23 '17 at 11:51

If you are interested in the number of users that have at least 1 row with (style IS TRUE) in access_rights, aggregate access_rights before you join:

SELECT count(style OR NULL) AS style_ct
FROM   users
JOIN  (
   SELECT user_id, bool_or(style) AS style
   FROM   access_rights
   GROUP  BY 1
   ) u USING (user_id);

Using JOIN, since users without any entries in access_rights don't count in this case.
Using the aggregate function bool_or().

Or even simpler:

SELECT count(*) AS style_ct
FROM   (
   SELECT user_id
   FROM   access_rights
   GROUP  BY 1
   HAVING bool_or(style)
   );

This is assuming a foreign key enforcing referential integrity, so there is no access_rights.user_id without a corresponding row in users.
Also assuming no NULL values in access_rights.user_id, which would increase the count by 1 - and can be countered by using count(user_id) instead of count(*).

Or (if that assumption is not true) use an EXISTS semi-join:

SELECT count( EXISTS (
              SELECT  1
              FROM    access_rights
              WHERE   user_id = u.user_id
              AND     style  -- boolean value evaluates on its own
              ) OR NULL
            )
FROM   users u;

I am using the capabilities of true boolean values to simplify the count and the WHERE clause. Details:
Compute percents from SUM() in the same SELECT sql query

Thanks Erwin, you always come through with some good answers I appreciate it! — jvans, Jul 01 '14 at 21:14
@jvans: Good to hear. :) Consider the even simpler variant I added. — Erwin Brandstetter, Jul 01 '14 at 21:23

Nicholas Carey · Answer 2 · 2014-07-01T19:38:00.963

You could do this:

Try something like this (per the documentation)

select sum( case style when TRUE then 1 else 0 end ) as style_count
from public.users         u
join public.access_rights ar on ar.user_id = u.user_id

Or, considering your problem statement, "I want to count the number of values in the style column that are true", you could do this:

select count(*) as style_count
from public.users         u
join public.access_rights ar on ar.user_id = u.user_id
where u.style = TRUE

Edited To Note:

On re-reading your question, it sounds like what you really want is a count of distinct users whose style attribute is true, and who have an access right. You can get to that by this:

select count(distinct u.user_id)
from public.users u
join public.access_rights ar on ar.user_id = u.user_id
where u.style = TRUE
;

Another way to get there would be like this:

select count(*)
from public.users u
where u.style = TRUE
  and exists ( select *
               from public.access_rights ar
               where ar.user_id = u.user_id
             )

I would vote for the latter as it more clearly shows your intent.

score 1 · Answer 3 · answered Jul 01 '14 at 19:51

It seems each one has a different understanding of the question. Here is mine

select
    count(case when style then 1 end) as classic_case,
    count(style or null) as boolean_count
from users u
where exists (
    select 1
    from access_rights
    where user_id = u.user_id
)

It will count the total number of trues for users which have access_rights.

SQL counting unique true values on join table

3 Answers3