Postgresql – Select total count and percent

postgresql

Say have table like:

user_uuid | value | state 
------------------+------+-------
uuid_1    |  111 | t
uuid_1    |  111 | f
uuid_1    |  111 | f
uuid_1    |  111 | f
uuid_2    |  333 | t
uuid_2    |  444 | t
uuid_2    |  555 | f
uuid_3    |  666 | t
uuid_4    |  777 | f
uuid_1    |  222 | t
uuid_1    |  222 | t

Task is select total count for each user_uuid and percent from total count to count with some value and state.
For example:
total count for uuid_1 is 6.

We need to get percent with value = 111 and status 'true'.

For 'uuid_1' count is 1. So need to calculate percent from 1 to 6

For 'uuid_2' and others counts are 0 so percents are 0 too.

I tryid this SQL:

 SELECT tq.user_uuid
     , COUNT(1) AS total
     , t.cnt
  FROM test_query tq
 CROSS
  JOIN (SELECT COUNT(user_uuid) AS cnt FROM test_query ) AS t
 GROUP
    BY tq.user_uuid, t.cnt;

But i cannot get how to select in JOIN part count for some conditions (like state=true, value=111)

Best Answer

You can use a SUM(CASE... for this purpose.

SELECT
    user_uuid,
    COUNT(*) cnt,
    ROUND (100.0 * (SUM(CASE WHEN value = '111' AND state = true THEN 1 ELSE 0 END)) / COUNT(*), 1) AS percent
FROM
    t
GROUP BY
    user_uuid;

user_uuid | cnt | percent
:-------- | --: | ------:
uuid_2    |   3 |     0.0
uuid_3    |   1 |     0.0
uuid_1    |   6 |    16.7
uuid_4    |   1 |     0.0

As a_horse_with_no_name has pointed out, you can use COUNT(*) FILTER(WHERE...) syntax too.

SELECT
    user_uuid,
    COUNT(*) cnt,
    ROUND (100.0 * (COUNT(*) FILTER (WHERE value = '111' AND state = true)) / COUNT(*), 1) AS percent
FROM
    t
GROUP BY
    user_uuid;

db<>fiddle here

Related Question