CODEWITHSUNDEEP
sqloracle-database
Cos
Cos

Reputation: 1709

Conditional count over multiple tables

Is there a simpler way to count the number of rows in different tables that fulfill the same conditions?

For example, I want to separately count the number of rows in the following two tables that correspond to certain IDs:

Select 
'table1' as tablename, count(*) as rownr from table1
where SOMEID in ('1815972751','1815751159','1815752967','1815756079')

union all

Select 
'table2' as tablename, count(*) as rownr from table2
where SOMEID in ('1815972751','1815751159','1815752967','1815756079')  ;

The result would be something like

table1 | 21
table2 | 54

However, I would like to only define the condition (in this case, the IDs) once, for example in a variable or list, so they area easily manageable.

Upvotes: 0

Views: 109

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Here is one way:

select tablename, count(*)
from (select 'table1' as tablename, someid
      from table1
      union all
      select 'table2' as tablename, someid
      from table2
     ) t
where someid in ('1815972751', '1815751159', '1815752967', '1815756079')
group by tablename;

Do note that performance may not be as good as in your original version.

Upvotes: 2

Related Questions