patternMinor
Display non-null and null count using case statement
Viewed 0 times
casenonstatementnullusingandcountdisplay
Problem
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:
Output:
Sample Data:
+------+
| Eid |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+Output:
+--------+--------+
| Eid | Counts |
+--------+--------+
| Number | 5 |
| Null | 5 |
+--------+--------+Solution
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:
My idea revolves around the idea that
Output 1:
If the specific arrangement of rows and columns is important, I would
Output 2:
Using
Output 3:
Edit:
This is the query form Babu ended up using:
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
CASESELECT
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 numbertestCode Snippets
CREATE TABLE #Example
(
Eid integer NULL
);
INSERT #Example
(Eid)
VALUES
(1),
(2),
(3),
(4),
(5),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL);SELECT
[Number] = COUNT(E.Eid),
[Null] = COUNT(*) - COUNT(E.Eid)
FROM #Example AS E;+--------+------+
| Number | Null |
+--------+------+
| 5 | 5 |
+--------+------+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;+--------+--------+
| Eid | Counts |
+--------+--------+
| Number | 5 |
| Null | 5 |
+--------+--------+Context
StackExchange Database Administrators Q#110869, answer score: 4
Revisions (0)
No revisions yet.