COUNT(USER_ID) Without GROUP BY

Question

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：不是一个单独的分组函数。

请帮助获取上述格式的数据。感谢您提前帮助

Answer 1

您可以使用：

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，因为这是一种数据类型。(

输出：

><1>

NO_OF_NAMES	NO_OF_ID	NO_OF_V_NAME	>NO_OF_USERS_WITH_NUMBER_DATA
4	4	3

这里有一个选项：

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>