CODEWITHSUNDEEP
sqloracle-database
Mark
Mark

Reputation: 9

How to get also the not existing values

I've got a query like this

select column, count(*)

from mytable

where column in ('XXX','YYY','ZZZ',....)

group by column;

But I want also to get a row for values the aren't in the table.

Let's suppose that 'ZZZ' doesn't exist in mytable, I'd like to get:

COLUMN COUNT(*)

XXX    3

YYY    2

ZZZ    0 (or NULL)

Oracle version 10g

Thanks in advance

Mark

Upvotes: 0

Views: 30

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

In general, you would need to have a second table which contains all the possible column values whose counts you want to appear in the output. For demo purposes only, we can use a CTE for that:

WITH vals AS (
    SELECT 'XXX' AS val UNION ALL
    SELECT 'YYY' UNION ALL
    SELECT 'ZZZ'
)

SELECT t1.val, COUNT(t2.col) AS cnt
FROM vals t1
LEFT JOIN mytable t2
    ON t2.col = t1.val
GROUP BY
    t1.val;

Upvotes: 1

Related Questions