3

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 |
+--------+--------+
4

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.