Count from more than one tables

s imam 21 Reputation points
2021-03-18T19:39:17.753+00:00

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

79358-image.png

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

79345-image.png

Transact-SQL
Transact-SQL
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
    2021-03-18T22:31:36.703+00:00

    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
    2021-03-19T04:00:53.623+00:00

    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.

    0 comments No comments

  3. s imam 21 Reputation points
    2021-03-21T00:33:09.357+00:00

    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,

    79883-image.png