CODEWITHSUNDEEP
sqloracle-databaseselectgroup-bysummary
Vaidotas
Vaidotas

Reputation: 175

Oracle select to get summary

Say I have two database tables T1 and T2. T1 has some column C1 (among others) with values, say 1, 1, 2, 2, 2, 3, null, null. T2 has column C2 (among others) with values, say 1, 1, 2, 4, 5, 5, null. I wish to get a summary of these two columns, i.e. in one query, if possible, get to know how many times each value (null included) occurred in both columns combined. In this case, 1 occurred 4 times, 2 occurred 4 times, 3 and 4 occurred once, 5 occurred twice and null occurred 3 times. I do not know in advance all the possible values in the columns.

Upvotes: 1

Views: 859

Answers (2)

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

Depending your table size, your data distribution and maybe the index eventually available on C1 and C2, you might expect better performances by using a query like the following, as Oracle don't have to build the full union of both table.

SELECT C, SUM(N) FROM
(
    SELECT C1 AS C, COUNT(*) AS N FROM T1 GROUP BY C1
    UNION ALL
    SELECT C2, COUNT(*) FROM T2 GROUP BY C2
)
GROUP BY C;

That being said, YMMV. So if this is critical, I would suggest you to carefully examine the query execution plan in order to choose the "right" solution for your particular case.

Upvotes: 2

Mureinik
Mureinik

Reputation: 311326

You need a group by on top of a union all query:

SELECT   value, COUNT(*)
FROM     (SELECT c1 AS value
          FROM   t1
          UNION ALL
          SELECT c2 AS value
          FROM   t2)
GROUP BY value

Upvotes: 2

Related Questions