How to extract summary data even if there are no returned rows?

Question

My current request calls something like this:

╔════════════════════════════════════════╗
║  A Monthly    123 123 123 123 123 123  ║
║  B Quarterly  123 123 123 123 123 123  ║
║  C SemiAnnual 123 123 123 123 123 123  ║
║  D Annual     123 123 123 123 123 123  ║
╚════════════════════════════════════════╝

The problem occurs when I do not get the string returned for a specific mode. When this happens, it simply turns off the mode.
I want him to show:

╔════════════════════════════════════════╗
║  A Monthly    123 123 123 123 123 123  ║
║  B Quarterly  0   0   0   0   0   0    ║
║  C SemiAnnual 123 123 123 123 123 123  ║
║  D Annual     123 123 123 123 123 123  ║
╚════════════════════════════════════════╝

I know there is a way to do this. Just draw a space. Something like pulling modes separately and then pulling the sums?

Here is my current request:

SELECT MODE, 
       SUM(POLCT)      AS POLCT, 
       SUM(RDRCNT)     AS RDRCNT, 
       SUM(INCCNT)     AS INCCNT, 
       SUM(INCINS)     AS INCINS, 
       SUM(INS_AMOUNT) AS INS_AMOUNT, 
       SUM (PREM)      AS PREM 
FROM   (SELECT CASE 
                 WHEN MODE = '12' THEN 'D Annual' 
                 WHEN MODE = '03' THEN 'B Quarterly' 
                 WHEN MODE = '06' THEN 'C SemiAnnual' 
                 WHEN MODE = '01' THEN 'A Monthly' 
                 ELSE ' ' 
               END                           AS MODE, 
               POLICY_COUNT * NEGATIVE       AS POLCT, 
               RIDER_COUNT * NEGATIVE        AS RDRCNT, 
               INCNT * NEGATIVE              AS INCCNT, 
               INS_AMOUNT * NEGATIVE * INCNT AS INCINS, 
               INS_AMOUNT * NEGATIVE         AS INS_AMOUNT, 
               PRSC * NEGATIVE               AS PREM 
        FROM   DIST_OF_ISSUES AS a 
               LEFT OUTER JOIN DIST_OF_ISSUES_PLANS AS b 
                            ON a.PLANID = b.PLANID 
               LEFT OUTER JOIN DIST_OF_ISSUES_TYPE_ORDER AS c 
                            ON b.TYPE = c.TYPE 
        WHERE  SUBSTRING(a.PLANID, 1, 4) NOT IN ( 
               '1020', '2599', '1600', '1601', 
               '2597', '2598' ) 
               AND ( a.MONTH < 4 
                     AND a.MONTH > 0 ) 
               AND a.YEAR = 2014) AS A 
GROUP  BY MODE 
ORDER  BY MODE 

Answer 1

This allowed to solve the problem:

Select 
MODE,  
SUM(POLCT) AS POLCT,
SUM(RDRCNT ) AS RDRCNT,  
SUM(INCCNT) AS INCCNT,  
SUM(INCINS ) AS INCINS,
SUM(INS_AMOUNT) AS INS_AMOUNT,
SUM (PREM) AS PREM  FROM 
(SELECT CASE             
when mode = '12' then 'D Annual'  
when mode = '03' then 'B Quarterly'  
when mode = '06' then 'C SemiAnnual'    
when mode = '01' then 'A Monthly'      
else ' ' end as MODE,      
POLICY_COUNT * NEGATIVE AS POLCT,    
RIDER_COUNT * NEGATIVE AS RDRCNT, 
Incnt * NEGATIVE AS INCCNT, 
INS_AMOUNT * NEGATIVE * Incnt AS INCINS, 
INS_AMOUNT * NEGATIVE AS INS_AMOUNT, 
PRSC * NEGATIVE AS PREM   
FROM    Dist_Of_Issues as a 
Left Outer Join Dist_Of_Issues_Plans as b on a.PlanID = b.PlanID  
Left Outer Join Dist_Of_Issues_Type_Order as c on b.Type = c.Type 
where substring(a.PlanID,1,4) not in ('1020','2599','1600','1601','2597','2598') and
(a.Month < 4 and a.Month > 0) and a.Year = 2014 
UNION ALL
Select distinct (CASE             
when mode = '12' then 'D Annual'  
when mode = '03' then 'B Quarterly'  
when mode = '06' then 'C SemiAnnual'    
when mode = '01' then 'A Monthly'      
else ' '         end) as MODE, 0 as POLCT, 0 as RDRCNT, 0 as INCCNT, 0 as INCINS, 0 as 
INS_AMOUNT, 0 as PREM from Dist_Of_Issues) as A   
Group BY Mode    ORDER BY Mode'

Answer 2

You need to create a pseudo-table with all the necessary values:

SELECT  *
FROM    (   SELECT 'D Annual' AS Mode UNION ALL
            SELECT 'B Quarterly' AS Mode UNION ALL
            SELECT 'C SemiAnnual' AS Mode UNION ALL
            SELECT 'A Monthly' AS Mode UNION ALL
            SELECT ' ' AS Mode
        ) AS m

, , :

SELECT  m.MODE, 
        SUM(a.POLCT) AS POLCT,
        SUM(a.RDRCNT ) AS RDRCNT, 
        SUM(a.INCCNT) AS INCCNT, 
        SUM(a.INCINS ) AS INCINS,
        SUM(a.INS_AMOUNT) AS INS_AMOUNT,
        SUM(a.PREM) AS PREM  
FROM    (   SELECT 'D Annual' AS Mode UNION ALL
            SELECT 'B Quarterly' AS Mode UNION ALL
            SELECT 'C SemiAnnual' AS Mode UNION ALL
            SELECT 'A Monthly' AS Mode UNION ALL
            SELECT ' ' AS Mode
        ) AS m
        (   SELECT  CASE            
                        WHEN mode = '12' THEN 'D Annual' 
                        WHEN mode = '03' THEN 'B Quarterly' 
                        WHEN mode = '06' THEN 'C SemiAnnual'   
                        WHEN mode = '01' THEN 'A Monthly'     
                        ELSE ' '         
                    END AS MODE,     
                    POLICY_COUNT * NEGATIVE AS POLCT,   
                    RIDER_COUNT * NEGATIVE AS RDRCNT,
                    Incnt * NEGATIVE AS INCCNT,
                    INS_AMOUNT * NEGATIVE * Incnt AS INCINS, 
                    INS_AMOUNT * NEGATIVE AS INS_AMOUNT, 
                    PRSC * NEGATIVE AS PREM 
            FROM    Dist_Of_Issues AS a
                    LEFT OUTER JOIN Dist_Of_Issues_Plans AS b 
                        ON a.PlanID = b.PlanID 
                    LEFT OUTER JOIN Dist_Of_Issues_Type_Order AS c 
                        ON b.Type = c.Type
            WHERE   SUBSTRING(a.PlanID,1,4) NOT IN ('1020','2599','1600','1601','2597','2598') 
            AND     (a.Month < 4 AND a.Month > 0) 
            AND     a.Year = 2014
        ) AS a  
            ON a.Mode = m.Mode
GROUP BY m.Mode    
ORDER BY m.Mode;

Answer 3

Add an "empty dataset" at the end of your query with the log number and use the correct line with the inner join.

something like that:

WITH a 
     AS (SELECT * 
         FROM   table2 
         UNION 
         SELECT * 
         FROM   table1) 
SELECT t1.* 
FROM   a T1 
       INNER JOIN (SELECT mode, 
                          Min(rn) RN 
                   FROM   a 
                   GROUP  BY mode)T2 
               ON T1.mode = T2.mode 
                  AND T1.rn = T2.rn 

Ypou can play with my example in SQL Fiddle .