Table_A 資料：
NAME USER_ID V_NAME NUMBER
AUTO 1 HOME 123
CAT 2 HAT 456
DOT 3 789
FAN 4 REG
我正在尋找類似的輸出
No of distinct users no.of ID's no.of V_Name no.of users with no.of
NUMBER column data users without NUMBER column data
4 4 3 3 1
詢問 ：
SELECT
count(distinct NAME) AS No_of_Names,
count (distinct ID) AS No_of_ID,
(select count(distinct ID) from TABLE_A where NUMBER is not null) as No_of_users_with_Number_Data,
(select count(distinct ID) from TABLE_A where NUMBER is null) as No_of_users_without_Number_Data,
count (distinct V_NAME) as No_of_V_NAME FROM TABLE_A;
但是通過上面的查詢我得到了錯誤：
ORA-00937: 不是單組群功能。
請幫助獲取上述格式的資料。提前感謝您的幫助
uj5u.com熱心網友回復：
您可以使用：
SELECT COUNT(DISTINCT NAME) AS No_of_Names,
COUNT(DISTINCT ID) AS No_of_ID,
COUNT(DISTINCT V_NAME) as No_of_V_NAME,
COUNT(DISTINCT CASE WHEN "NUMBER" IS NOT NULL THEN ID END)
AS No_of_users_with_Number_Data,
COUNT(DISTINCT CASE WHEN "NUMBER" IS NULL THEN ID END)
as No_of_users_without_Number_Data
FROM table_a;
其中，對于樣本資料：
CREATE TABLE table_a (name, id, v_name, "NUMBER") as
SELECT 'AUTO', 1, 'HOME', 123 FROM DUAL UNION ALL
SELECT 'CAT', 2, 'HAT', 456 FROM DUAL UNION ALL
SELECT 'DOT', 3, NULL, 789 FROM DUAL UNION ALL
SELECT 'FAN', 4, 'REG' , NULL FROM DUAL;
（注意：你不應該為你的列命名NUMBER，因為那是一種資料型別。）
輸出：
NO_OF_NAMES NO_OF_ID NO_OF_V_NAME NO_OF_USERS_WITH_NUMBER_DATA NO_OF_USERS_WITHOUT_NUMBER_DATA 4 4 3 3 1
如果您隨后添加額外的行：
INSERT INTO table_a VALUES ('DOT', 3, NULL, NULL);
INSERT INTO table_a VALUES ('FAN', 4, 'XYZ', 321);
那么查詢輸出為：
NO_OF_NAMES NO_OF_ID NO_OF_V_NAME NO_OF_USERS_WITH_NUMBER_DATA NO_OF_USERS_WITHOUT_NUMBER_DATA 4 4 4 4 2
（注意：最后兩列的輸出是不同用戶的計數，而不是行數。）
db<>在這里擺弄
uj5u.com熱心網友回復：
這是一個選項：
SQL> with table_a (name, user_id, v_name, num) as
2 (select 'auto', 1, 'home', 123 from dual union all
3 select 'cat' , 2, 'hat' , 456 from dual union all
4 select 'dot' , 3, null , 789 from dual union all
5 select 'fan' , 4, 'reg' , null from dual
6 )
7 select
8 count(distinct name) cnt_1,
9 count(distinct user_id) cnt_2,
10 sum(case when v_name is not null then 1 else 0 end) cnt_3,
11 sum(case when num is not null then 1 else 0 end) cnt_4,
12 sum(case when num is not null then 0 else 1 end) cnt_5
13 from table_a;
CNT_1 CNT_2 CNT_3 CNT_4 CNT_5
---------- ---------- ---------- ---------- ----------
4 4 3 3 1
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/414337.html
標籤：
上一篇：Oracle會自動洗掉臨時表嗎？