Count from more than one tables

s imam 21 Reputation points

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' ...


A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 105.9K Reputation points MVP

    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.

    0 comments No comments

  2. EchoLiu-MSFT 14,576 Reputation points

    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.


    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.

    0 comments No comments

  3. s imam 21 Reputation points


    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,