Query not producing correct results and Group By Error

Query not producing correct results and Group By Error

• Ravi Kumar p

  Here's a second look at what I've sruggled with:

   I think we're almost there:
  
  Let's work with account # '4264287999172303'
  
  Query #1 - shows that there are 2 records in the RFINANL table for that account
  ------------
  SELECT rm.rmsacctnum AS [Rms Acct Num],  
          rf.rmstranamt as [Rms rmstranamt],
      rf.rmsbalance AS [Rms Balance]
  FROM RMASTER rm
  INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
  where rm.rmsacctnum = '4264287999172303'
  
  RESULTS:
  4264287999172303    2878.95     0.00
  4264287999172303    25910.55     28789.50
  
  
  Query #2 - shows that there are 2 records in the RFINANL table for that account where RMSTRANCDE = '10'
  -----------
  select rm.rmsacctnum AS [Rms Acct Num], rf.rmstranamt
  FROM RMASTER rm
  INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
  where rf.RMSTRANCDE = '10'
  and rm.rmsacctnum = '4264287999172303'
  
  RESULTS:
  4264287999172303    2878.95
  4264287999172303    25910.55
  
  
  Now this is what boggles my mind.  If we were to take Query #1 and add a sum in it like below, you would get a result of 2878.95 + 25910.55 but I don't.
  
  SELECT rm.rmsacctnum AS [Rms Acct Num],  
         sum(rf.rmstranamt) as [Rms rmstranamt], <------ added sum hoping to get 2878.95 + 25910.55
                    rf.rmsbalance AS [Rms Balance]
  FROM RMASTER rm
  INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
  where rm.rmsacctnum = '4264287999172303'
  GROUP BY  rm.rmsacctnum, rf.rmsbalance     <------ but now I'm required to group by any values that do not contain an aggregate function
  
  RESULTS:
  4264287999172303         2878.95     0.00
  4264287999172303         25910.55     28789.50
  
  Take this a step further, now I add a sum to rf.rmsbalance since at this point I don't know what else do do.  I then figure I should get 2878.95 + 25910.55 and 0.00 + 28.789.50 but again, I don't, I end up with both of them havin the same value.  I have no idea why:
  
  SELECT rm.rmsacctnum AS [Rms Acct Num],  
         sum(rf.rmstranamt) as [Rms rmstranamt],
      Sum(rf.rmsbalance) AS [Rms Balance]                   <------- Added sum to rf.rmsbalance
  FROM RMASTER rm
  INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
  where rm.rmsacctnum = '4264287999172303'
  GROUP BY  rm.rmsacctnum                                        <------- took out rf.rmsbalance because it's a sum now in the select
  
  RESULTS:
  
  4264287999172303         28789.50     28789.50
  
  
  So at this point I'm still not getting this which I am ultimately wanting for each and every account if you we were to figure out the correct syntax:
  
  (2878.95 + 25910.55) - (28789.50) as [Current Balance],
  ....

  
  
  

• DogGuts

  The error message is correct as you would have to enclose the rf10.rmstranamnt10 inside a sum() to allow for the aggregation.  I always recheck the SQL with an english translation of what I am trying to do.  Your descriptive phrase shows the secondary sum, but the T-SQL does not have it.

   

  I hope this helps,

   

  
  

• SteveInMA

  I did as you said and added the sum.  I am still getting a Current Balance of 0.00 for an account # I checked when I know for a fact there are a total of 2  positive rmstranamt records in RFINANL with a RMSTRANCDE of 10 so I should have the sume of those in my results for that account number but it's 0.00

  This query shows why

  SELECT rm.rmsacctnum AS [Rms Acct Num],

  sum(rf.rmstranamt) as Sum_rstranamt,

  sum(rf10.rmstranamt10) as Sum_rmstranamt_10,

  (sum(rf.rmstranamt) - sum(rf10.rmstranamt10)) AS [Current Balance]

  FROM RMASTER rm

  INNER JOIN

  (

  SELECT RMSFILENUM, SUM(rmstranamt) AS rmstranamt10

  FROM RFINANL

  WHERE RMSTRANCDE = '10'

  GROUP BY RMSFILENUM

  ) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

  INNER JOIN

  (

  SELECT RMSFILENUM, SUM(rmstranamt) AS rmstranamt

  FROM RFINANL

  GROUP BY RMSFILENUM

  ) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

  AND rm.rmsacctnum = '4264287999172303'

  GROUP BY rm.rmsacctnum

   results:

  4264287999172303     28789.50    28789.50    0.00

  Sum_rstranamt should not be 28789.50 because there are 2 records for that account:

  SELECT rm.rmsacctnum AS [Rms Acct Num],

  rf.rmstranamt as [Rms rmstranamt],

  rf.rmsbalance AS [Rms Balance]

  FROM RMASTER rm

  INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM

  where rm.rmsacctnum = '4264287999172303'

  4264287999172303     2878.95     0.00
  4264287999172303     25910.55   28789.50

   So Sum_rstranamt should be 2878.95 + 25910.55

  
  
  
• Query not producing correct results and Group By Error