0

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)

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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