PG: How to show total count for this query if result is 0

Question

I have a postgres 9.6 table called selected_media, with a column called _type that is set to either book or movie.

I want to select the COUNT of each type, in 1 single row, like this:

25 | 715

To do this, I have this query:

SELECT
  COALESCE(SUM(CASE WHEN _type = 'book'  THEN 1 ELSE 0 END), 0) AS books_count,
  COALESCE(SUM(CASE WHEN _type = 'movie' THEN 1 ELSE 0 END), 0) AS movies_count
FROM selected_media
WHERE subscriber_id = $1

The problem is 2 fold:

1. I want each column to show the respective total count for that _type only if the sum in that column happens to be 0 (none selected). This is because the UI/UX states "selecting 0 books or movies is the same as selecting all of them".

2. It needs to be fast, this table will have millions of rows in it

Answer 1

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 <> 0 THEN books_count 
    ELSE (another_value_or_expression_for_books) 
    END  AS books_count,
    CASE 
    WHEN movies_count <> 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)                                                                                                                                     |
|     ->  Aggregate  (cost=159.94..159.95 rows=1 width=8) (actual time=1.485..1.485 rows=1 loops=1)                                                             |
|           ->  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)                                                                                                                                     |
|     ->  Aggregate  (cost=4.15..4.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)                                                                 |
|           ->  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)                                                                                                                          |
|     ->  Aggregate  (cost=821.53..821.54 rows=1 width=8) (actual time=0.670..0.671 rows=1 loops=1)                                                  |
|           ->  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                                                                                                             |
|                 ->  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)                                                                                                                          |
|     ->  Aggregate  (cost=821.53..821.54 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)                                                  |
|           ->  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))                                                                 |
|                 ->  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