Oracle-11g

如何在計數(列)中包含零?

  • February 17, 2015

我正在嘗試為客戶建構報告。我需要顯示 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