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.

CodePudding user response：

The question is still unclear, but maybe you are after something like

select v.A, count(t.b)
from (select 1 as A union select 2 union select 3 union select 4) as v 
left join t on t.a = v.a 
group by 1 

CodePudding user response：

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