T-SQL Group by When empty ResultSet

how can select count in group by when empty ResultSet and part record

Table T schema is column A int,column B int

data demo A is

A    B
1    11
1    2
1    Null
2    1
2    21
3    12
3    Null

data demo B is

A    B
2    1
2    21
3    12
3    Null
4    11
4    2
4    Null

data demo C is

A    B
(empty record)

I want select to this ResultSet

A    B
1    Bcount
2    Bcount
3    Bcount
4    Bcount

my sql code is

select A,Count(B) FROM T
group by A

when data demo A I lost A=4 result

when data demo B I lost A=1 result

when data demo C I lost all result,ResultSet is empty.

Solution 1:

You can left join to VALUES

SELECT V.A, COUNT(T.B) AS Total_B
FROM (VALUES (1),(2),(3),(4)) V(A)
LEFT JOIN T ON (T.A = V.A) 
GROUP BY V.A
ORDER BY V.A;

Or create a reference table for the values of A. Then left join to the reference table.

CREATE TABLE REF_A (A INT PRIMARY KEY);
INSERT INTO REF_A (A) VALUES (1),(2),(3),(4);

SELECT A.A, COUNT(T.B) AS Total_B
FROM REF_A A
LEFT JOIN T ON (T.A = A.A) 
GROUP BY A.A
ORDER BY A.A;

Demo on db<>fiddle here