# Select total count and percent

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)

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

• Alternatively: `count(*) filter (where ...)` Jul 2 '19 at 15:00
• Is it possible use with JOIN? Jul 2 '19 at 15:19
• Yes it is, but don't you think it is more clear in this way? Jul 2 '19 at 16:05