CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-administration
sqllover2020
sqllover2020

Reputation: 21

Combining the Result of Two Select SQL Queries involving Joins

I need to combine these two SQL queries to find the total count of empty & non empty tables in a given schema.

The below query produces two result outputs - which is correct.

But how I can join two of these select statements together as one single query statement with two result outputs?

I have tried using Union, Union all, Intersect all, and it will not give me the results I am looking for.

--- query for Empty tables: 
select count (*) from 
(
select schema_name(tab.schema_id) + '.' + tab.name as [emptytable]
   from sys.tables tab
        inner join sys.partitions part
            on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
having sum(part.rows) = 0 
) as subquery;

--- query for Non-Empty tables: 
select count (*) from 
(
select schema_name(tab.schema_id) + '.' + tab.name as [non_emptyTable]
   from sys.tables tab
        inner join sys.partitions part
            on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
having sum(part.rows) > 0 
) as subquery;

Upvotes: 1

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use conditional aggregation:

select sum(case when size = 0 then 1 else 0 end) as num_empty,
       sum(case when size > 0 then 1 else 0 end) as num_nonempty
from (select schema_name(tab.schema_id) + '.' + tab.name as table_name,
             sum(part.rows) as size
      from sys.tables tab join
           sys.partitions part
           on tab.object_id = part.object_id
      where part.index_id in (1, 0) -- 0 - table without PK, 1 table with PK
      group by schema_name(tab.schema_id) + '.' + tab.name
     ) t

Upvotes: 1

Related Questions