如何在計數（列）中包含零？

我正在嘗試為客戶建構報告。我需要顯示 Tab1、Col1（驗證表）中的所有值、每次發生的計數以及總數的百分比。這就是我要找的東西：

Number of records       Type        Percentage of total
2                       A           2
3                       B           3
0                       C           0

我已經完成了基本查詢，我只是不知道如何包含零計數！這是我到目前為止所擁有的：

select count(b.Col1) "Number of Records",
a.col2 "Type",
to_char(round(ratio_to_report(count(a.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a,
Tab2 b
where Tab2.Col1 = Tab1.Col1(+)
group by a.Col1;

提前致謝。

編輯：嘗試這樣做：

select count(b.Col1) "Number of Records", 
a.col2 "Type",
to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab2 b
left join Tab1 a on tab2.col1 = tab1.col1 -- or (from tab2 left join tab1) if that is what's intended
group by a.Col1;

給了我與我的查詢相同的結果。

這：

select COALESCE(count(b.Col1), 0) AS "Number of Records", 
a.col2 "Type",
to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1
group by a.Col1;

也給了我同樣的結果。

編輯2：

表 A 列：ID、程式碼、描述

表 B 列：ID、RecordNumber、TableA_ID

表 B 是“主”，表 A 是眾多“細節”之一。基本上我需要 TableB 中的 TableA 實例的計數 .. 包括零。

給我想要的結果的查詢：

select coalesce(count(b.TabA_id),0) as "num records",
a.desc "type",
to_char(round(ratio_to_report(count(b.TabA_id)) over()*100)) || '%' as "Percentage of Total"
from TabB b
right join TabA a on b.TabA_id = a.id
group by  b.TabA_id, a.id
order by a.desc;

您提供的資訊不多，所以這只是一個猜測。

您的 WHERE 子句Tab2.Col1 = Tab1.Col1(+)可能是向後的，關於 (+) …我建議將其切換為 LEFT JOIN 無論如何，只是為了使其更具可讀性..

select
 count(b.Col1) "Number of Records", --- CHANGED a.Col1 to b.Col1
 a.col2 "Type",
 to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1 -- or (from tab2 left join tab1) if that is what's intended
group by a.Col1;

如果您仍然沒有得到 0 計數，那麼您應該嘗試使用 coalesce 語句。

select
 COALESCE(count(b.Col1), 0) AS "Number of Records", --- COALESCE HERE .. CHANGED a.Col1 to b.Col1
 a.col2 "Type",
 to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1
group by a.Col1;

但是，您沒有在 sql 中的任何地方引用表 b - 請解釋如果您不打算使用它的數據，為什麼需要加入它？

引用自：https://dba.stackexchange.com/questions/93152