none
SUM of COUNTS across multiple tables RRS feed

  • Question

  • Hi, I need to return the sum of counts across multiple tables which have been partitioned. For example, a single partition in the parent table could contain;

    PartitionRowNumber | Test1 | Test2 | UniqueID

    1 |  ABC | DEF | 123456

    2 |  ABC | DEF | 789101

    And associated child table 1;

    PartitionRowNumber | Test1 | Test2 | ParentUniqueID

    1 |  ABC | DEF | 123456

    2 |  ABC | DEF | 789101

    And associated child table 2;

    PartitionRowNumber | Test1 | Test2 | ParentUniqueID

    1 |  ABC | DEF | 123456

    1 |  ABC | DEF | 123456

    1 |  ABC | DEF | 123456

    And what I require is the total count of related records (for each partition) in all child tables, so in this example, a total of 5.

    However, when I create CTE and right join these tables, and calculate count

    SELECT COUNT(table1.ParentUniqueID) + COUNT(table2.ParentUniqueID)....

    I always return the total of the child table with the most records, in this example, table 2, I receive 3.

    Any help appreciated

    Monday, June 24, 2019 11:54 AM

All replies

  • Why not just UNION ALL the tables and COUNT GROUP BY UniqueID?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 24, 2019 12:05 PM
    Answerer
  • CREATE TABLE child1(
       PartitionRowNumber INTEGER  NOT NULL  
      ,Test1              VARCHAR(6) NOT NULL
      ,Test2              VARCHAR(5) NOT NULL
      ,ParentUniqueID     INTEGER  NOT NULL
    );
    INSERT INTO child1(PartitionRowNumber,Test1,Test2,ParentUniqueID) VALUES 
    (1,'ABC','DEF',123456),
     (2,'ABC','DEF',789101);
    
    
     CREATE TABLE parent(
       PartitionRowNumber INTEGER  NOT NULL  
      ,Test1              VARCHAR(6) NOT NULL
      ,Test2              VARCHAR(5) NOT NULL
      ,UniqueID     INTEGER  NOT NULL
    );
    INSERT INTO parent(PartitionRowNumber,Test1,Test2,UniqueID) VALUES 
    (1,'ABC','DEF',123456),
     (2,'ABC','DEF',789101);
    
     CREATE TABLE child2(
       PartitionRowNumber INTEGER  NOT NULL  
      ,Test1              VARCHAR(6) NOT NULL
      ,Test2              VARCHAR(5) NOT NULL
      ,ParentUniqueID     INTEGER  NOT NULL
    );
    INSERT INTO child2(PartitionRowNumber,Test1,Test2,ParentUniqueID) VALUES 
    (1,'ABC','DEF',123456),
    (1,'ABC','DEF',123456),
    (1,'ABC','DEF',123456);
     
     
    
    
     ;with mycte as 
     (
      select 0 as fromtable, PartitionRowNumber,Test1,Test2, UniqueID from parent
     union all
     select 1  ,PartitionRowNumber,Test1,Test2,ParentUniqueID from child1
     union all
     select 2, PartitionRowNumber,Test1,Test2,ParentUniqueID from child2
    
     )
     Select count(PartitionRowNumber) cnt, UniqueID  
     from mycte
     group by  UniqueID
    
    
    
     drop table  child1,child2,parent

    Monday, June 24, 2019 2:06 PM
    Moderator
  • Hi DapperDev_,

     

    Per your description, I am not sure which your requirement is. So please try following two script.

     
    CREATE TABLE child1(
       PartitionRowNumber INTEGER  NOT NULL  
      ,Test1              VARCHAR(6) NOT NULL
      ,Test2              VARCHAR(5) NOT NULL
      ,ParentUniqueID     INTEGER  NOT NULL
    );
    INSERT INTO child1(PartitionRowNumber,Test1,Test2,ParentUniqueID) VALUES 
    (1,'ABC','DEF',123456),
     (2,'ABC','DEF',789101);
    
    
     CREATE TABLE parent(
       PartitionRowNumber INTEGER  NOT NULL  
      ,Test1              VARCHAR(6) NOT NULL
      ,Test2              VARCHAR(5) NOT NULL
      ,UniqueID     INTEGER  NOT NULL
    );
    INSERT INTO parent(PartitionRowNumber,Test1,Test2,UniqueID) VALUES 
    (1,'ABC','DEF',123456),
     (2,'ABC','DEF',789101);
    
     CREATE TABLE child2(
       PartitionRowNumber INTEGER  NOT NULL  
      ,Test1              VARCHAR(6) NOT NULL
      ,Test2              VARCHAR(5) NOT NULL
      ,ParentUniqueID     INTEGER  NOT NULL
    );
    INSERT INTO child2(PartitionRowNumber,Test1,Test2,ParentUniqueID) VALUES 
    (1,'ABC','DEF',123456),
    (1,'ABC','DEF',123456),
    (1,'ABC','DEF',123456);
    
    ;with cte as 
    ( select * from child1
     union all
     select * from child2
     )
     select a.PartitionRowNumber,a.ParentUniqueID,count(*) ct from cte a 
     join parent b 
     on a.PartitionRowNumber=b.PartitionRowNumber
     and a.ParentUniqueID=b.UniqueID
     group by  a.PartitionRowNumber,a.ParentUniqueID
     /*
     PartitionRowNumber ParentUniqueID ct
    ------------------ -------------- -----------
    1                  123456         4
    2                  789101         1
     */
    
     ;with cte as 
    ( select * from child1
     union all
     select * from child2
     )
     select count(*) ct from cte a 
     /*
     ct
    -----------
    5
     */
    

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 25, 2019 3:38 AM