How do I return empty if two columns are 0?-CodePudding

I have the following Query:

SELECT a.timestamp AS start, a.timestamp   (1 * INTERVAL '1 hour') AS end,
                           count_1, count_2                              
FROM (
    SELECT time_bucket_gapfill(CAST(1 * INTERVAL '1 hour' AS INTERVAL), time) AS timestamp,
    COALESCE(COUNT(DISTINCT CASE WHEN type IN ('Type 1') THEN time END), 0) count_1,
    COALESCE(COUNT(DISTINCT CASE WHEN type IN ('Type 2') THEN time END), 0) count_2
    FROM info
    JOIN person USING (id)
    WHERE time >= '2022-04-29 22:00:00.0'
        AND time < '2022-04-30 22:00:00.0'
    GROUP BY timestamp) a
ORDER BY a.timestamp ASC

The query gives me something like this:

|        start        |          end        | count_1 | count_2 |
-----------------------------------------------------------------
| 2022-04-29 22:00:00 | 2022-04-29 23:00:00 |    0    |    0    |
| 2022-04-29 23:00:00 | 2022-04-30 00:00:00 |    0    |    0    |
| 2022-04-30 00:00:00 | 2022-04-30 01:00:00 |    0    |    0    |
| 2022-04-30 01:00:00 | 2022-04-30 02:00:00 |    0    |    0    |
| 2022-04-30 02:00:00 | 2022-04-30 03:00:00 |    0    |    0    |
...

Only thing I want is to know if there is a way to say that if ALL rows in count_1 and count_2 is 0 then don't return anything. I just want an empty result back.. Is that possible?

CodePudding user response：

You can put the subquery a into a WITH query, and use another subquery based on this to filter the results out such as

WITH a AS
(
 SELECT time_bucket_gapfill(CAST(1 * INTERVAL '1 hour' AS INTERVAL),
                            time) AS timestamp,
        COALESCE(COUNT(DISTINCT CASE
                         WHEN type IN ('Type 1') THEN
                          time
                       END),
                 0) count_1,
        COALESCE(COUNT(DISTINCT CASE
                         WHEN type IN ('Type 2') THEN
                          time
                       END),
                 0) count_2
   FROM info
   JOIN person
  USING (id)
  WHERE time >= '2022-04-29 22:00:00.0'
    AND time <  '2022-04-30 22:00:00.0'
  GROUP BY timestamp
)
SELECT timestamp AS start, timestamp   (1 * INTERVAL '1 hour') AS "end",
       count_1, count_2                              
  FROM a
 WHERE (SELECT SUM(count_1 count_2) FROM a)!=0  
ORDER BY a.timestamp ASC

CodePudding user response：

You may add WHERE EXISTS condition below to check if there are any records having 1-0, 1-1, or 0-1 values of count_1 and count_2 returned or not.

WHERE EXISTS (SELECT 1 
    FROM (
        COALESCE(COUNT(DISTINCT CASE WHEN type IN ('Type 1') THEN time END), 0) count_1,
        COALESCE(COUNT(DISTINCT CASE WHEN type IN ('Type 2') THEN time END), 0) count_2
        FROM info JOIN person USING (id)
        WHERE time >= '2022-04-29 22:00:00.0'
              AND time < '2022-04-30 22:00:00.0'
        GROUP BY timestamp) ip 
    WHERE count_1 <> 0 OR count_2 <> 0)