Display non-null and null count using case statement

casecountnulloracle

In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below:

Sample Data:

+------+
| Eid  |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+

Output:

+--------+--------+
|  Eid   | Counts |
+--------+--------+
| Number |      5 |
| Null   |      5 |
+--------+--------+

Best Answer

This may not directly help you, since I only know SQL Server, but in any case this is how I would approach this:

Sample Data:

CREATE TABLE #Example
(
    Eid integer NULL
);

INSERT #Example
    (Eid)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (NULL),
    (NULL),
    (NULL),
    (NULL),
    (NULL);

My idea revolves around the idea that COUNT(column) does not count NULLs, whereas COUNT(*) does:

SELECT 
    [Number] = COUNT(E.Eid), 
    [Null] = COUNT(*) - COUNT(E.Eid)
FROM #Example AS E;

Output 1:

+--------+------+
| Number | Null |
+--------+------+
|      5 |    5 |
+--------+------+

If the specific arrangement of rows and columns is important, I would UNPIVOT:

WITH Previous AS
(
    -- Same query as above
    SELECT 
        [Number] = COUNT(E.Eid), 
        [Null] = COUNT(*) - COUNT(E.Eid)
    FROM #Example AS E
)
SELECT
    U.Eid,
    U.Counts
FROM Previous
UNPIVOT
(
    Counts FOR Eid IN ([Number], [Null])
) AS U;

Output 2:

+--------+--------+
|  Eid   | Counts |
+--------+--------+
| Number |      5 |
| Null   |      5 |
+--------+--------+

Using CASE

SELECT 
    C.Name, 
    Counts = COUNT(*) 
FROM 
(
    SELECT 
        Name =
            CASE 
                WHEN E.Eid IS NULL 
                THEN 'Null' 
                ELSE 'Number' 
            END 
    FROM #Example AS E
) AS C
GROUP BY
    C.Name;

Output 3:

+--------+--------+
|  Name  | Counts |
+--------+--------+
| Null   |      5 |
| Number |      5 |
+--------+--------+

Edit:

This is the query form Babu ended up using:

select 
    count(*) as cnt, 
    case 
        when smpth is null 
        then 'NULL' 
        else 'NUMBER' 
    end numbertest 
from employees 
group by 
    case 
        when smpth is null 
        then 'NULL' 
        else 'NUMBER' 
    end numbertest
Related Question