none
Count the rows for a column by using another Table RRS feed

  • Question

  • Hello, 

    I need to create a view to list count of error for Projects.

    I am having two Table, 1. Version Table  2. QC Error Table.

    Schema for Version Table is:

           [Id]
          ,[Project Name]
          ,[Project_ID]
          ,[Version]
          ,[Type]
          ,[Active]

    for this, I have used as 

    and Schema for QC error Table is:

          [ID]
          ,[Staff]
          ,[Project_ID]
          ,[Version_ID]  
          ,[Type Of Error]

    I need to Create a View based on Project_ID and count of Version ID like

    Project ID Count of Version ID
    Project1 25
    Project2 0
    Project3 2
    Project4 12

    for the above result, I Used following command 

    SELECT COUNT(dbo.[QC Errors].Version_ID) AS Expr1, dbo.[QC Errors].Project_ID
    FROM            dbo.[QC Errors] INNER JOIN
                             dbo.[Version Table] ON dbo.[QC Errors].Version_ID = dbo.[Version Table].Id
    GROUP BY dbo.[QC Errors].Project_ID

    i got the result but its shows only NON ZERO Data. Because QC Error Table having only Error Log record, Some Of Projects are not having single error. Which need to display as a count OF ZERO value.

    Can any one help me to resolve this issue.

    Thanks

    TalhaTJ


    Tuesday, September 3, 2019 10:56 AM

Answers

  • dbo.[QC Errors] LEFT JOIN

    OR

    dbo.[QC Errors] RIGHT JOIN


    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


    Tuesday, September 3, 2019 1:00 PM
    Moderator
  • Hi TalhaTJ,

    Hope the below code-example can help you.

    CREATE TABLE [VERSION TABLE]
    (
           [Id] INT
          ,[Project Name] VARCHAR(150)
          ,[Project_ID] INT
          ,[Version] INT
          ,[Type] INT
          ,[Active] BIT
    );
    
    CREATE TABLE [QC ERROR]
    (
           [ID] INT
          ,[Staff] VARCHAR(150)
          ,[Project_ID] INT
          ,[Version_ID] INT  
          ,[Type Of Error] INT
    );
    
    INSERT INTO [VERSION TABLE] ([Id],[Project Name],[Project_ID],[Version],[Type],[Active])
    VALUES 
    (1,'PROJECT_A',11,101,1,1), --Failure Record as it has some entries in [QC ERROR] table.
    (2,'PROJECT_B',12,102,1,1); --Success record as it not has any entries in [QC ERROR] table.
    
    INSERT INTO [QC ERROR] ([Id],[Staff],[Project_ID],[Version_ID],[Type Of Error])
    VALUES 
    (1001,'STAFF_A',11,1,400), 
    (1002,'STAFF_A1',11,1,404); 
    
    --Method 1 (Using LEFT JOIN)
    SELECT V.[Project Name] AS [Project ID],COUNT(Q.[Project_ID]) AS [Count of Version ID]
    FROM [VERSION TABLE] V LEFT JOIN [QC ERROR] Q ON Q.[Version_ID]=V.[Id]
    GROUP BY V.[Project Name];
    
    --Method 2 (Using OUTER APPLY)
    SELECT [VERSION TABLE].[Project Name] AS [Project ID],COUNT([QC ERROR].[Project_ID]) AS [Count of Version ID]
    FROM
       [VERSION TABLE]
       OUTER APPLY (
          SELECT [Project_ID]
          FROM [QC ERROR]
          WHERE [VERSION TABLE].Id = [QC ERROR].[Version_ID]
       ) [QC ERROR] 
    GROUP BY [VERSION TABLE].[Project Name];
    
    --Method 3 (Using RIGHT JOIN)
    SELECT V.[Project Name] AS [Project ID],COUNT(Q.[Project_ID]) AS [Count of Version ID]
    FROM [QC ERROR] Q RIGHT JOIN [VERSION TABLE] V
    ON Q.[Version_ID]=V.[Id]
    GROUP BY V.[Project Name];

    Note: Suggested some of the many ways that is easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Tuesday, September 3, 2019 7:43 PM

All replies

  • dbo.[QC Errors] LEFT JOIN

    OR

    dbo.[QC Errors] RIGHT JOIN


    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


    Tuesday, September 3, 2019 1:00 PM
    Moderator
  • Because you want all versions, you need your query to be:

    …
    FROM            dbo.[Version Table] 
    LEFT OUTER JOIN dbo.[QC Errors] ON dbo.[QC Errors].Version_ID = dbo.[Version Table].Id

    Tuesday, September 3, 2019 2:34 PM
    Answerer
  • Hi TalhaTJ,

    Hope the below code-example can help you.

    CREATE TABLE [VERSION TABLE]
    (
           [Id] INT
          ,[Project Name] VARCHAR(150)
          ,[Project_ID] INT
          ,[Version] INT
          ,[Type] INT
          ,[Active] BIT
    );
    
    CREATE TABLE [QC ERROR]
    (
           [ID] INT
          ,[Staff] VARCHAR(150)
          ,[Project_ID] INT
          ,[Version_ID] INT  
          ,[Type Of Error] INT
    );
    
    INSERT INTO [VERSION TABLE] ([Id],[Project Name],[Project_ID],[Version],[Type],[Active])
    VALUES 
    (1,'PROJECT_A',11,101,1,1), --Failure Record as it has some entries in [QC ERROR] table.
    (2,'PROJECT_B',12,102,1,1); --Success record as it not has any entries in [QC ERROR] table.
    
    INSERT INTO [QC ERROR] ([Id],[Staff],[Project_ID],[Version_ID],[Type Of Error])
    VALUES 
    (1001,'STAFF_A',11,1,400), 
    (1002,'STAFF_A1',11,1,404); 
    
    --Method 1 (Using LEFT JOIN)
    SELECT V.[Project Name] AS [Project ID],COUNT(Q.[Project_ID]) AS [Count of Version ID]
    FROM [VERSION TABLE] V LEFT JOIN [QC ERROR] Q ON Q.[Version_ID]=V.[Id]
    GROUP BY V.[Project Name];
    
    --Method 2 (Using OUTER APPLY)
    SELECT [VERSION TABLE].[Project Name] AS [Project ID],COUNT([QC ERROR].[Project_ID]) AS [Count of Version ID]
    FROM
       [VERSION TABLE]
       OUTER APPLY (
          SELECT [Project_ID]
          FROM [QC ERROR]
          WHERE [VERSION TABLE].Id = [QC ERROR].[Version_ID]
       ) [QC ERROR] 
    GROUP BY [VERSION TABLE].[Project Name];
    
    --Method 3 (Using RIGHT JOIN)
    SELECT V.[Project Name] AS [Project ID],COUNT(Q.[Project_ID]) AS [Count of Version ID]
    FROM [QC ERROR] Q RIGHT JOIN [VERSION TABLE] V
    ON Q.[Version_ID]=V.[Id]
    GROUP BY V.[Project Name];

    Note: Suggested some of the many ways that is easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Tuesday, September 3, 2019 7:43 PM
  • SELECT v.[Project_ID], SUM(CASE WHEN e.[Version_ID] IS NULL THEN 0 ELSE 1 END) AS [Count of Version ID]
    FROM dbo.[Version Table] AS v
    LEFT JOIN dbo.[QC Errors] AS e ON e.[Version_ID] = v.[ID]
    GROUP BY v.[Project_ID];

    A Fan of SSIS, SSRS and SSAS



    • Edited by Guoxiong Tuesday, September 3, 2019 8:42 PM
    Tuesday, September 3, 2019 8:39 PM
  • Hi Arulmouzhi / All

    thanks for your reply.

    All method which are given by you is working fine in On Premises SQL server. Please see the output as below:

    while the same query is not working on Azure SQL database. I am getting below error.

    Can you help me to resolve this.


    • Edited by TalhaTJ Wednesday, September 4, 2019 12:01 PM
    Wednesday, September 4, 2019 12:01 PM
  • Hi TalhaTJ,

    Hope the below script can help you on this.

    SET ANSI_WARNINGS OFF;
    GO
    
    SELECT [VERSION TABLE].[Project Name] AS [Project ID],COUNT([QC ERROR].[Project_ID]) AS [Count of Version ID]
    FROM
       [VERSION TABLE]
       OUTER APPLY (
          SELECT [Project_ID]
          FROM [QC ERROR]
          WHERE [VERSION TABLE].Id = [QC ERROR].[Version_ID]
       ) [QC ERROR] 
    GROUP BY [VERSION TABLE].[Project Name];

    Note: Suggested one of the many ways that is easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi
    Wednesday, September 4, 2019 5:30 PM