Group By Sum and without Group by sum Amount is different

72 Views Asked by At

I have 2 SQL queries.

Query #1:

SELECT 
    SUM(PrincipalBalance)
FROM (
    SELECT 
         lt.AccountId,
         SUM(CASE 
                 WHEN TransactionTypeId IN (1)
                     THEN PrincipalPortionAmount
                 ELSE 0
             END) 
         - SUM(CASE 
                   WHEN TransactionTypeId NOT IN (1, 2)
                       THEN PrincipalPortionAmount
                       ELSE 0
               END) AS PrincipalBalance
     FROM 
         program.LoanTransaction lt
     INNER JOIN 
         program.LoanAccount la ON lt.AccountId = la.Id
     WHERE 
         BranchId = 301
         AND TransactionDate <= 20231231000000
         AND la.STATUS <> - 1
     GROUP BY 
         lt.AccountId
     HAVING  
         SUM(Debit - Credit) > 1
) T

Query #2:

SELECT
    ISNULL(SUM(CASE 
                   WHEN TransactionTypeId IN (1) 
                       THEN PrincipalPortionAmount 
                   ELSE 0 
               END), 0)
    - ISNULL(SUM(CASE
                     WHEN TransactionTypeId IN (43, 38, 4, 12, 7, 10)
                         THEN PrincipalPortionAmount 
                     ELSE 0 
                 END), 0)
FROM
    program.LoanTransaction  lt
INNER JOIN
    program.LoanAccount la ON lt.AccountId = la.Id
WHERE
    BranchId = 301 
    AND TransactionDate <= 20231231000000 
    AND la.Status <> -1

Query #1 result is
80773498.0599999

Query #2 result is
81060946.8400006

But both results should be the same. I don't get it, why the differences? How can I find out what is causing the differences?

0

There are 0 best solutions below