Count from more than one tables

Question

My using below script

Select Count(*) as Count, sum(Page) as PageCount, min(page) as minPage, Max(page) as maxPage, Avg(page) as avgPage from table1 Where substring(ID,14.1) = '7' and year >'2016'

My question is can I use sys.tables name instead of running script one by one for each table
Select name from sys.tables where name like '%Master%'

Report should looks like below

I am also trying to add range column which is group by page 0-25,26-50,...
Select sum(page) as range from table 1 Where substring(ID,14.1) = '7' and year >'2016' and page between '0' and '25' ...

Answer 1

In a relational database a table is supposed to model a unique entity with a unique set of attributes. From this perspective, your question is a little odd.

But it is true, sometimes you have entities that are different enough from each to other to make it practical to have them in the same table, but they still have a couple of attributes in common.

What you could do is to set up a view that encompasses all tables:

CREATE VIEW MyTenTables AS
   SELECT 'table1' AS tablename, ID, pages, year
   FROM   table1
   UNION ALL 
   SELECT 'table2' AS tablename, ID, pages, year
   FROM   table2
   ...

Then you can run your queries over this view.

Answer 2

Hi @s imam ,

Welcome to the Microsoft TSQL Q&A Forum!

In addition to views, you can also use cte:

    ;with cte  
    as(select 'table1'  tablename, ID, pages, year  
       from  table1  
       union all   
       select 'table2'  tablename, ID, pages, year  
       from  table2  
       ...)  
      
    Select count(*) as RecordsTotal,  
    sum(cast(pages as INT)) as PageCount,  
    sum(cast(pages as INT)) over(partition by pages order by pages) as Rage,  
    Min(cast(pages as INT)) as minPages,  
    Max(cast(pages as INT)) as maxPages,  
    AVG(cast(pages as INT)) as AVGPages   
    from cte Where substring(ID,12,1)='9' and Year > '2016'   

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Echo,

For some reason I am getting total of all tables. Not group by tables.

Also I don't understand 'over(partition by pages order by pages) as Rage,