We help IT Professionals succeed at work.

TSQLL not returning information

mpdillon
mpdillon asked
on
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:
use table aliases for all column references:
SELECT     d.mn_no, d.sb_no, d.dp_no, d.trx_amt, d.trx_dt,
                          (SELECT     SUM(T.trx_Amt)
                            FROM          GLTrxFil_SQL T
                            WHERE      T.MN_No = d.MN_No AND T.SB_no = d.SB_No AND T.DP_No = d.DP_No
) AS EndingBalance
FROM         GLTRXFIL_SQL d
WHERE     (d.trx_src = 'BBF')
ORDER BY d.mn_no, d.sb_no, d.dp_no, d.trx_dt

Open in new window

Author

Commented:
That was it.
Thank you,
pat

Explore More ContentExplore courses, solutions, and other research materials related to this topic.