fitziano fitziano - 1 month ago 8
SQL Question

Oracle SQL Common Entries in two Columns

I have a table that contains common entries in two columns.

For Example:

Column1 Column2
Entry1 || NULL
Entry2 || Entry1
Entry3 || Entry1
Entry4 || Entry4
Entry5 || NULL


I want to find how many times the entries of column 1 appear in column 2.

So the result would be something like:

Column1 Count
Entry1 || 2
Entry2 || 0
Entry3 || 0
Entry4 || 1
Entry5 || 0

Answer
WITH counts AS (
    SELECT column2, COUNT(*) AS the_count
      FROM x
      GROUP BY column2
)
SELECT x.column1, COALESCE(c.the_count, 0)
  FROM x LEFT OUTER JOIN counts c ON (x.column1 = c.column2)
  ORDER BY 1
Comments