Oracle-11g
如何在計數(列)中包含零?
我正在嘗試為客戶建構報告。我需要顯示 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 - 請解釋如果您不打算使用它的數據,為什麼需要加入它?