You can't select the values from the table when the row count is 0. Where would it get the values for the nonexistent rows?

To do this, you'll have to have another table that defines your list of valid Project and Financial_Year values. You'll then select from this table, perform a left join on your existing table, then do the grouping.

Something like this:

SELECT l.Project, l.Financial_Year, COUNT(t.Project) AS HighRiskCount
INTO #HighRisk 
FROM MasterRiskList l
left join #TempRisk1 t on t.Project = l.Project and t.Financial_Year = l.Financial_Year
WHERE t.Risk_1 = 3
GROUP BY l.Project, l.Financial_Year

Aggregate functions ignore null values.

So

SELECT COUNT(cola) AS thecount
FROM tablea

is equivalent to

SELECT count(*) AS thecount
FROM tablea
WHERE cola IS NOT NULL;

As all of your values are null, count(cola) has to return zero.

If you want to count the rows that are null, you need count(*)

SELECT cola,
       count(*) AS theCount
FROM tablea
WHERE cola is null
GROUP BY cola;

Or simpler:

SELECT count(*) AS theCount
FROM tablea
WHERE cola is null;

If you want to count NULL and NOT NULL values in a single query, use:

SELECT count(cola) as not_null_count, 
       count(case when cola is null then 1 end) as null_count
FROM tablea;

This works for Oracle and SQL Server (you might be able to get it to work on another RDBMS):

select sum(case when a is null then 1 else 0 end) count_nulls
     , count(a) count_not_nulls 
  from us;

Or:

select count(*) - count(a), count(a) from us;

In the outer query, you can replace a NULL with a zero using the IFNULL() function, e.g.

SELECT ...
     , IFNULL(v.t1count,0) AS t1count
  FROM ...
  LEFT
  JOIN ( SELECT ... AS t1count
                ...
       ) v
    ON ...

The NULL you are getting returned by the outer query isn't from the inline view query. The NULL is a result of "no match" being found by the LEFT [OUTER] JOIN operation.

If you are referencing v.t1count in other expressions in the outer query, you can replace those references with NULLIF(v.t1count,0) as well.

The reason for that is because you have provided count with a column value which is null. Instead, use count(*):

SELECT COUNT(*) FROM Table_Name WHERE [Current_Status] IS NULL

Sample data:

current_status
--------------
Processed
Null
Not Processed
Null

And the difference between two queries:

count(current_status)

SELECT count(current_status) FROM table_name WHERE current_status IS NULL 

0

count(*)

SELECT count(*) FROM table_name WHERE current_status IS NULL 

2

I am not very clear on your question however you can alter your code as below for your null question:

select U.Mail, count(isnull(P.ID, 0)) as PostCount, 
    case 
        when count(P.ID) = 0 then 'none' --doesn't work
        when (count(P.ID) <= 2 and count(P.ID) > 0) then 'few'
        when (count(P.ID) <= 5 and count(P.ID) > 2 )then 'medium'
        else 'many'
    end PostCountCategory
from Users U 
left join Tag T on U.Mail = T.Mail
left join Post P on T.IDPost = P.ID
group by U.Mail

Alternatively you can use count(isnull(P.ID, 0)) inside your case statement. Hope this helps.

As suggested by Mustaccio, you may use coalesce also instead of isnull.