PG: How to show total count for this query if result is 0
Then do it slightly different, use the (more or less) complementary of COALESCE, which is NULLIF:

SELECT
    NULLIF(books_count, 0) AS books_count,
    NULLIF(movies_count, 0) AS movies_count
FROM
    ( -- Original, slightly simplified query
    SELECT
        SUM(CASE WHEN _type = 'book'  THEN 1 END) AS books_count,
        SUM(CASE WHEN _type = 'movie' THEN 1 END) AS movies_count
    FROM 
        selected_media
    WHERE 
        subscriber_id = 1234
    ) AS s1 ;


dbfiddle here

If you don't want to have a NULL value when the result is zero, but another one, you can use:

SELECT
    CASE 
    WHEN books_count &lt;&gt; 0 THEN books_count 
    ELSE (another_value_or_expression_for_books) 
    END  AS books_count,
    CASE 
    WHEN movies_count &lt;&gt; 0 THEN movies_count 
    ELSE (another_value_or_expression_for_movies) 
    END  AS movies_count
FROM
    ( -- Original, slightly simplified query
    SELECT
        SUM(CASE WHEN _type = 'book'  THEN 1 END) AS books_count,
        SUM(CASE WHEN _type = 'movie' THEN 1 END) AS movies_count
    FROM 
        selected_media
    WHERE 
        subscriber_id = 1234
    ) AS s1 ;


You can also use count(case ...) instead of SUM, and, in latest versions of PostgreSQL, you can use a FILTER, instead of CASE:

SELECT
    NULLIF(books_count, 0) AS books_count,
    NULLIF(movies_count, 0) AS movies_count
FROM
    (SELECT
        count(*) FILTER (WHERE _type = 'book') AS books_count,
        count(*) FILTER (WHERE _type = 'movie') AS movies_count
    FROM 
        selected_media
    WHERE 
        subscriber_id = 1234
    ) AS s1 ;


dbfiddle here



If you need the fastest possible speed, and depending on the number of _types, and the number of elements of each type, you could use some partial indexes:

CREATE INDEX idx_books ON selected_media (subscriber_id) WHERE _type='book' ;
CREATE INDEX idx_movies ON selected_media (subscriber_id) WHERE _type='movie' ;


And then perform your query as two subqueries:

SELECT
    nullif( (select count(*) from selected_media where _type='book'  
             and subscriber_id = 1234), 
           0) AS count_books,
    nullif( (select count(*) from selected_media where _type='movie'  
             and subscriber_id = 1234), 
           0) AS count_movies ;


This will give you a query plan doing two index only scans most of the time.


| QUERY PLAN                                                                                                                                                    |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Result  (cost=164.10..164.12 rows=1 width=16) (actual time=1.491..1.492 rows=1 loops=1)                                                                       |
|   InitPlan 1 (returns $0)                                                                                                                                     |
|     -&gt;  Aggregate  (cost=159.94..159.95 rows=1 width=8) (actual time=1.485..1.485 rows=1 loops=1)                                                             |
|           -&gt;  Index Only Scan using idx_books on selected_media  (cost=0.28..150.36 rows=3833 width=0) (actual time=0.016..1.108 rows=3974 loops=1)           |
|                 Index Cond: (subscriber_id = 1234)                                                                                                            |
|                 Heap Fetches: 3974                                                                                                                            |
|   InitPlan 2 (returns $1)                                                                                                                                     |
|     -&gt;  Aggregate  (cost=4.15..4.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)                                                                 |
|           -&gt;  Index Only Scan using idx_movies on selected_media selected_media_1  (cost=0.12..4.14 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1) |
|                 Index Cond: (subscriber_id = 1234)                                                                                                            |
|                 Heap Fetches: 0                                                                                                                               |
| Planning time: 0.094 ms                                                                                                                                       |
| Execution time: 1.514 ms                                                                                                                                      |


dbfiddle here



If you have very many _type, or you want your query to work with any possible _type (and not two specifically chosen) your second choice would be to have the following index:

CREATE INDEX idx_subscriber_type ON selected_media (subscriber_id, _type) ;


... then, the previous query will execute using this index


| QUERY PLAN                                                                                                                                         |
| :------------------------------------------------------------------------------------------------------------------------------------------------- |
| Result  (cost=1643.07..1643.09 rows=1 width=16) (actual time=0.679..0.680 rows=1 loops=1)                                                          |
|   InitPlan 1 (returns $0)                                                                                                                          |
|     -&gt;  Aggregate  (cost=821.53..821.54 rows=1 width=8) (actual time=0.670..0.671 rows=1 loops=1)                                                  |
|           -&gt;  Bitmap Heap Scan on selected_media  (cost=29.98..819.19 rows=933 width=0) (actual time=0.170..0.573 rows=992 loops=1)                |
|                 Recheck Cond: ((subscriber_id = 1234) AND (_type = 'book'::text))                                                                  |
|                 Heap Blocks: exact=547                                                                                                             |
|                 -&gt;  Bitmap Index Scan on idx_subscriber_type  (cost=0.00..29.75 rows=933 width=0) (actual time=0.113..0.113 rows=992 loops=1)      |
|                       Index Cond: ((subscriber_id = 1234) AND (_type = 'book'::text))                                                              |
|   InitPlan 2 (returns $1)                                                                                                                          |
|     -&gt;  Aggregate  (cost=821.53..821.54 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)                                                  |
|           -&gt;  Bitmap Heap Scan on selected_media selected_media_1  (cost=29.98..819.19 rows=933 width=0) (actual time=0.005..0.005 rows=0 loops=1) |
|                 Recheck Cond: ((subscriber_id = 1234) AND (_type = 'movie'::text))                                                                 |
|                 -&gt;  Bitmap Index Scan on idx_subscriber_type  (cost=0.00..29.75 rows=933 width=0) (actual time=0.005..0.005 rows=0 loops=1)        |
|                       Index Cond: ((subscriber_id = 1234) AND (_type = 'movie'::text))                                                             |
| Planning time: 0.077 ms                                                                                                                            |
| Execution time: 0.706 ms                                                                                                                           |


dbfiddle here