COUNT(USER_ID) Without GROUP BY-CodePudding

Table_A Data :

NAME   USER_ID   V_NAME   NUMBER   

AUTO   1         HOME      123
CAT    2         HAT       456
DOT    3                   789
FAN    4         REG

I was looking for output like

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

Query :

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;

But with above query I am getting error :

ORA-00937: not a single-group group function.

Please help in getting the data in above format. Thanks for your help in advance

CodePudding user response：

Here's one option:

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>

CodePudding user response：

You can use:

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;

Which, for the sample data:

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;

(Note: You should not name your columns NUMBER as that is a data type.)

Outputs:

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

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

If you then add extra rows:

INSERT INTO table_a VALUES ('DOT', 3, NULL, NULL);
INSERT INTO table_a VALUES ('FAN', 4, 'XYZ', 321);

Then the query output is:

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

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

(Note: the output in the last two columns is a count of distinct users and not a count of rows.)

db<>fiddle here