How to display empty rows in Microsoft SQL Server?

You will need to select from a 'State' table to get the list of states, then join to your count to get the totals and use a coalesce to substitute 0 when there is no data returned from the datawarehouse. Simple example below:

create table #state (state varchar(1))

create table #datawarehouse (state varchar(1), data int)


insert into #state 
    values   ('a')
            ,('b')
            ,('c')
            ,('d')

insert into #datawarehouse(state, data)
    values ('a', 1)
            ,('a',2)
            ,('a',3)
            ,('b',1)

SELECT State.State, COALESCE(DataWarehouse.c, 0)
FROM   #State state
         LEFT OUTER JOIN (  SELECT  State, Count(*) c
                            FROM    #DataWareHouse datawarehouse
                            GROUP BY State) Datawarehouse
              ON   State.State = DataWareHouse.State

Create a list of all states, left join your data to it. Count.

SELECT allstates.StateAbbr, count (data.StateAbbr)
FROM 
  (select distinct StateAbbr from datawarehouse) allstates 
  LEFT JOIN
  DataWarehouse data
  ON 
    allstates.stateabbr = data.stateabbr and
    data.FiscalYear = 2017 and
    data.MaltreatmentSetCode NOT IN('999', '') 

GROUP BY allstates.StateAbbr
ORDER BY allstates.StateAbbr ASC

If all values of StateAbbr that we want to return appear in rows in DataWarehouse table, we can use conditional aggregation.

As a demonstration:

SELECT dw.StateAbbr
     , SUM(CASE WHEN dw.FiscalYear = 2017 AND dw.MaltreatmentSetCode != '999' AND dw.MaltreatmentSetCode != '' THEN 1 ELSE 0 END) AS cnt
  FROM DataWarehouse dw
 GROUP BY dw.StateAbbr
 ORDER BY dw.StateAbbr ASC

Note that for each row, the CASE expression is going to be evaluated, returning either 0 or 1, and the SUM() aggregate will add the 1s and 0s up to get us a count.

If there's values of StateAbbr that should be returned by that are not in the DateWarehouse table, then we need a rowsource for those values. (Frequently, these values are in "dimension" tables in a data mart.)

Assuming Abbr is unique in the dwstate table... if this query returns the distinct list of state abbr we need to return:

SELECT s.Abbr
  FROM dwstate s
ORDER BY s.Abbr  

Then we could do something like this:

SELECT s.Abbr  AS StateAbbr 
     , SUM(CASE WHEN dw.FiscalYear = 2017 AND dw.MaltreatmentSetCode != '999' AND dw.MaltreatmentSetCode != '' THEN 1 ELSE 0 END) AS cnt
  FROM dwstate s 
  LEFT 
  JOIN DataWarehouse dw
    ON dw.StateAbbr = s.Abbr
 GROUP BY s.Abbr
 ORDER BY s.Abbr ASC

These examples omit the bit about the subquery; it's not clear (to me) which part of the specification that part is meant to satisfy.

    SELECT DISTINCT StateAbbr
      FROM DataWarehouse
     WHERE (NOT MaltreatmentSetCode = '999' )
       AND FiscalYear = 2017

If that subquery is meant to return the set of StateAbbr that we are supposed to return, then we can use that as a rowsource in place of dwstate ...

SELECT s.Abbr  AS StateAbbr 
     , SUM(CASE WHEN dw.FiscalYear = 2017 AND dw.MaltreatmentSetCode != '999' AND dw.MaltreatmentSetCode != '' THEN 1 ELSE 0 END) AS cnt
  FROM ( -- unique list of state abbreviations 
         SELECT n.StateAbbr AS Abbr
           FROM DataWarehouse n
          WHERE (NOT n.MaltreatmentSetCode = '999' )
            AND n.FiscalYear = 2017
          GROUP BY n.StateAbbr
       ) s
  LEFT 
  JOIN DataWarehouse dw
    ON dw.StateAbbr = s.Abbr
 GROUP BY s.Abbr
 ORDER BY s.Abbr ASC