Get rows as a result of a non-null query and at the same time count the number of rows by columns that did not hit but they are also not null

Question

I want to take all records into account when calculating the count for num_zaprosa and num_otveta, but not show them in the result set if one of them is null. Is it possible to do this? here is my code, but it didn't help me solve my problem:

WITH counted_records AS (
    SELECT num_zaprosa, num_otveta,
        COUNT(*) AS count_all_records,
        SUM(CASE WHEN num_zaprosa IS NOT NULL THEN 1 ELSE 0 END) AS count_num_zaprosa,
        SUM(CASE WHEN num_otveta IS NOT NULL THEN 1 ELSE 0 END) AS count_num_otveta
    FROM your_table
    GROUP BY num_zaprosa, num_otveta
)
SELECT num_zaprosa, num_otveta, count_all_records, count_num_zaprosa, count_num_otveta
FROM counted_records
WHERE num_zaprosa IS NOT NULL OR num_otveta IS NOT NULL;

initial data:

num_zaprosa|num_otveta
______________________
 2344      |   200          

  22       |   null  
 
 2344      |    200

 155       |    6

 22        |    9999

what result do I expect:

num_zaprosa|num_otveta| count_num_zaprosa| count_num_otveta |
_____________________________________________________________
 2344      |   200    |        2         |          2       |
_____________________________________________________________
 22        |   9999   |        2         |          1       |
_____________________________________________________________
   155     |     6    |         1        |          1       |

Answer 1

One option is to use conditional Count() Over() analytic functions.

WITH      -- S a m p l e    D a t a :
  tbl AS
    ( Select  2344 as num_zaprosa, 200 as num_otveta  From RDB$DATABASE Union All 
      Select  22, null From RDB$DATABASE Union All 
      Select  2344, 200  From RDB$DATABASE Union All 
      Select  155,  6  From RDB$DATABASE Union All 
      Select  22, 9999  From RDB$DATABASE
   )

--    M a i n    S Q L :
SELECT   num_zaprosa, num_otveta, 
         Max(count_num_zaprosa) as count_num_zaprosa,
         Max(count_num_otveta) as count_num_otveta
FROM  ( Select   d.num_zaprosa, d.num_otveta,
                 Count(Case When t.num_zaprosa = d.num_zaprosa Then 1 End) Over(Partition By t.num_zaprosa, t.num_otveta) as count_num_zaprosa, 
                 Count(Case When t.num_otveta = d.num_otveta Then 1 End) Over(Partition By t.num_zaprosa, t.num_otveta) as count_num_otveta
       From    ( Select Distinct num_zaprosa, num_otveta 
                 From tbl
               ) d 
       Inner Join tbl t ON(  (t.num_zaprosa = d.num_zaprosa And t.num_otveta = d.num_otveta) 
                           OR
                            (t.num_zaprosa = d.num_zaprosa And t.num_otveta Is Null)
                         )
    )
WHERE     num_zaprosa Is Not Null And num_otveta Is Not Null
GROUP BY  num_zaprosa, num_otveta

/*      -- R e s u l t :
NUM_ZAPROSA NUM_OTVETA  COUNT_NUM_ZAPROSA   COUNT_NUM_OTVETA
----------- ----------  -----------------   ----------------
         22       9999                  2                  1
        155          6                  1                  1
       2344        200                  2                  2    */

See the fiddle here.