We help IT Professionals succeed at work.
Get Started

TSQLL not returning information

mpdillon
mpdillon asked
on
335 Views
Last Modified: 2012-05-12
I owuld like the following query to return the EndingBalance as part of the query. The EndingBalance should be returned as part of the embedded Select statement.

This part of the Select returns the correct information. SELECT     mn_no, sb_no, dp_no, trx_amt, trx_dt,

But when I embed the second SELECT statement, the second SELECT statement is not returning the information I want. I must be doing something wrong as the Select Sum(T.Trx_Amt)... statement, as it is returning all 0's. It is as if the embedded SELECT statement is returning the value for all the records in the table rather than the individual totals for MN_No, SB_No and Dp_No combinations. This is a yearly General Ledger table and the sum of all the transactions must equal 0.

SELECT     mn_no, sb_no, dp_no, trx_amt, trx_dt,
                          (SELECT     SUM(T.trx_Amt)
                            FROM          GLTrxFil_SQL T
                            WHERE      T.MN_No = MN_No AND T.SB_no = SB_No AND T.DP_No = DP_No
) AS EndingBalance
FROM         GLTRXFIL_SQL
WHERE     (trx_src = 'BBF')
ORDER BY mn_no, sb_no, dp_no, trx_dt

How do I fix the link between the records to return the sum of all the records with the same MN_No and SB_No and DP_No values?

Thanks,
pat
Comment
Watch Question
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE