PostgreSQL - select count(*) for rows where a condition holds

Question

I have the following table and some sample records in it:

  id  | attr1_id | attr2_id |      user_id      | rating_id | override_comment
------+----------+----------+-------------------+-----------+------------------
 1    |      188 |      201 | [email protected] |         3 |
 2    |      193 |      201 | [email protected] |         2 |
 3    |      193 |      201 | [email protected] |         1 |
 4    |      194 |      201 | [email protected] |         1 |
 5    |      194 |      201 | [email protected] |         1 |
 6    |      192 |      201 | [email protected] |         1 |

The combination of (attr1_id, attr2_id, user_id) is UNIQUE, meaning each user can only create one record with a specific pair of attribute ids.

My goal is to count the number of rows where rating_id = 1, but only count each combiniation of attr1_id and attr2_id only once, and only where there doesn't exist any other row (by other users) that have rating_id > 1 and refer to the same attr1_id and attr2_id. Note that the combination of attr1_id and attr2_id can be switched around, so given these two records:

  id  | attr1_id | attr2_id |      user_id       | rating_id | override_comment
------+----------+----------+--------------------+-----------+------------------
  20  |       5  |       2  | [email protected]  |         3 |
------+----------+----------+--------------------+-----------+------------------
  21  |       2  |       5  | [email protected]  |         1 |

no row should be counted, as the rows refer to the same combination of attr_ids and one of them has rating_id > 1.

However, if these two rows exist:

  id  | attr1_id | attr2_id |      user_id       | rating_id | override_comment
------+----------+----------+--------------------+-----------+------------------
  20  |       5  |       2  | [email protected]  |         1 |
------+----------+----------+--------------------+-----------+------------------
  21  |       2  |       5  | [email protected]  |         1 |
------+----------+----------+--------------------+-----------+------------------
  22  |       2  |       5  | [email protected]  |         1 |

all rows should only be counted as one, because they all share the same combination of attr1_id and attr2_id and all have rating_id = 1.

My approach so far is this, but it results in no rows being selected at all.

SELECT *
FROM compatibility c
WHERE rating_id > 1
  AND NOT EXISTs
    (SELECT *
     FROM compatibility c2
     WHERE c.rating_id > 1
       AND (
             (c.attr1_id = c2.attr1_id) AND (c.attr2_id = c2.attr2_id)
             OR
             (c.attr1_id = c2.attr2_id) AND (c.attr2_id = c2.attr1_id)
           )
    )

How can I achieve this?

Erwin Brandstetter · Accepted Answer · 2023-04-24 10:53:39Z

My goal is to count the number of rows where rating_id = 1, but only count each combiniation of attr1_id and attr2_id only once, and only where there doesn't exist any other row (by other users) that have rating_id > 1.

Building on your original

Your original query was on the right track to exclude offending rows. You just had > instead of =, and the count was missing, yet.

SELECT count(*) AS ct
FROM  (
   SELECT 1
   FROM   compatibility c
   WHERE  rating_id = 1
   AND    NOT EXISTS (
      SELECT 1
      FROM   compatibility c2
      WHERE  c2.rating_id > 1
      AND   (c2.attr1_id = c.attr1_id AND c2.attr2_id = c.attr2_id OR
             c2.attr1_id = c.attr2_id AND c2.attr2_id = c.attr1_id))
   GROUP  BY least(attr1_id, attr2_id), greatest(attr1_id, attr2_id)
   ) sub;

Shorter and faster

SELECT count(*) AS ct
FROM  (
   SELECT FROM compatibility  -- empty SELECT list is enough for count(*)
   GROUP  BY least(attr1_id, attr2_id), greatest(attr1_id, attr2_id)
   HAVING max(rating_id) <= 1
   ) sub;

Similar to this earlier answer with more explanation.
HAVING max(rating_id) <= 1 implements your requirement exactly.

fiddle
_{Old sqlfiddle}

Gordon Linoff · Accepted Answer · 2014-11-03 17:11:40Z

1

If I understand correctly, you want pairs of attributes whose ratings are always "1".

This should give you the attributes:

select least(attr1_id, attr2_id) as a1, greatest(attr1_id, attr2_id) as a2,
       min(rating_id) as minri, max(rating_id) as maxri
from compatibility c
group by least(attr1_id, attr2_id), greatest(attr1_id, attr2_id)
having min(rating_id) = 1 and max(rating_id) = 1;

To get the count, just use this as a subquery:

select count(*)
from (select least(attr1_id, attr2_id) as a1, greatest(attr1_id, attr2_id) as a2,
             min(rating_id) as minri, max(rating_id) as maxri
      from compatibility c
      group by least(attr1_id, attr2_id), greatest(attr1_id, attr2_id)
      having min(rating_id) = 1 and max(rating_id) = 1
     ) c

answered Nov 3, 2014 at 17:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

doque Over a year ago

Wonderful, thank you. How would I be able to not only select the count(*), but instead select the c.id of the compatibilities that need rating yet?

Gordon Linoff Over a year ago

Just put min(c.id)in the subquery and run it alone, without the outer query.

doque Over a year ago

I misphrased - I meant to ask how to select all these distinct pairs of (attr1_id and attr2_id), that still need rating. Is this possible?

Gordon Linoff Over a year ago

@doque . . . I think you should ask another question.

doque Over a year ago

And so I have: stackoverflow.com/questions/26908868/…

Clodoaldo Neto · Accepted Answer · 2014-11-04 10:14:47Z

1

In Postgresql this do it. SQLFiddle nor working right now:

select count(*)
from (
    select least(attr1_id, attr2_id), greatest(attr1_id, attr2_id)
    from compatibility
    group by 1, 2
    having not bool_or(rating_id > 1)
) s
;
 count 
-------
     2
(1 row)

edited Nov 4, 2014 at 10:14

answered Nov 3, 2014 at 18:33

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

Mike Dinescu · Accepted Answer · 2014-11-03 17:27:07Z

0

I would use a CASE .. WHEN to re-arrange the attributes such that the smaller one is always first and the order on that. Example query to follow..

SELECT attrSmall, 
       attrLarge,            
       MAX(rating_id) as ratingMax
  FROM (
   SELECT CASE WHEN c.attr1_id < c.attr2_id 
               THEN c.attr1_id 
               ELSE c.attr2_id END as attrSmall,
          CASE WHEN c.attr1_id < c.attr2_id 
               THEN c.attr2_id 
               ELSE c.attr1_id END as attrLarge,
          c.rating_id
    FROM compatibility c) as c1
  GROUP BY atrrSmall, attrLarge
  HAVING ratingMax = 1

edited Nov 3, 2014 at 17:27

answered Nov 3, 2014 at 17:11

Mike Dinescu

56k17 gold badges124 silver badges153 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - select count(*) for rows where a condition holds

4 Answers 4

Building on your original

Shorter and faster

Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Building on your original

Shorter and faster

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related