Calculating Numerators and Denominators

Transact-SQL
1

Good evening,

I have a rather peculiar issue while trying to calculate Numerator and Denominator for a Final Temporary Table I have built (using a couple of Intermediary Temporary Tables along the way).

ATTEMPT 1

The following two lines were my initial attempt (on the Final Temp):

cast(SUM(CASE When Then 1 Else 0 End) as varchar) AS 'Numerator',

Count(*) AS 'Denominator'

While the Numerator was spot-on, the Denominator contained woefully small figures.

ATTEMPT 2

So next, I calculated the denominator creating an additional LEFT JOIN directly on the Source table in the database (which is what Final Temp is actually based on anyway). There is no filtering going on in the intermediary tables from the first to Final Temp – just a series of LEFT JOINs with other Source tables.

LEFT JOIN (Select….Count(*) As 'Denominator' from )

This time the Count(*) from this subquery provided a vastly larger – and also incorrect – Denominator:

ATTEMPT 3

I finally tried an additional LEFT JOIN of the Final Temp with itself e.g.

LEFT JOIN (Select….Count(*) As 'Denominator' from ).

This time, Both Numerator and Denominator were vastly inflated!

The correct Numerator should be 40-something (which is achieved in both Attempts 1 & 2 during the full run of the script), but the Denominator has been incorrect in all three attempts and needs to be around 1700. In fact, I do achieve around 1700 when I independently run either of the two subqueries comprising both additional LEFT JOINS used Attempts 1 & 3 in a separate script:

Select….Count(*) As 'Denominator' from

Select….Count(*) As 'Denominator' from

I would appreciate insight in to what is happening.

2

without any data or examples, I would say to generate a query that calcs the denominator correctly, then see how to incorporate it into the main query. I would say it has to do with records being omitted by a join

3

Hope this helps

SELECT  
    SUM(CASE   
        When Condition Then 1   
        Else 0   
    End) AS Numerator,  
    (SELECT COUNT(*) FROM SourceTable WHERE YourConditions) AS Denominator  
FROM FinalTempTable;

Likely Issue:

The problem appears to be related to how you're counting records for the denominator. Here's what might be happening:

  1. COUNT(*) in Final Temp Table:
  • When you use COUNT(*) directly on the final temp table, it only counts the rows that made it through all the joins and filters in your intermediary tables
  • This is why the denominator appears smaller than expected
  1. LEFT JOIN with Source Table:
  • When you join back to the source table, you might be introducing duplicate rows or including records that shouldn't be there
  • This could explain why the count becomes larger than expected
  1. Self-Join:
  • Joining the final temp table to itself can create a Cartesian product
  • This inflates both numerator and denominator because it multiplies the row count