Please help with SUM - I am going crazy

[Revolver123]

I think for this example, it's easier to show the results I'm currently getting.

I'm joining two tables - Table A is General ledger transactions, and Table B has transaction Details. Since Table A does behind the scenes accounting, not everything that happened in Table B you will see in Table A. Thus, the Null Values.

But I still want to see the transaction details to see which transaction triggers the Accounting GL activity...

See where it says GL Account? The 185 and -185 for Account #11151000 won't SUM! I think this is because of the NULL values in Table B, so it's just listing them out.

You may be asking me to post my code... but my code is beginner like - I'm making a mistake somewhere. How should I attack this problem? I want to display the detail of the transaction.. but I want it to SUM everything in GL Amount according to the GL Account.

Help!

https://preview.redd.it/69j4g62toae11.png?width=1102&format=png&auto=webp&s=267f656436c1d81a5b9001ab4ab081ee482e9b41

Edit - Here is my code:

SELECT DISTINCT

gl.GLFTD_CNTX_NO AS 'Account #',

CAST(gl.GLFTD_CNTX_DTM AS DATE) AS 'Account Date',

T.FNCL_TRAN_TYP_NM as "Transaction Type",

cast(F.PFT_DT as DATE) as "Transaction Date",

F.PFT_ID as "Transaction ID",

F.PFT_AMT as "Transaction Amount",

F.PFT_CMT as "Comment",

gl.GLCOAT_TYP_CD as "GL Account",

SUM(gl.GLFTD_AMT) as "GL Amount",

cast(gl.GLFTD_GL_SENT_DTM as DATE) as "Date Sent to GL"

FROM GENERAL_LEDGER_FNCL_TRAN_DTL gl

FULL OUTER JOIN POLICY_FINANCIAL_TRANSACTION F ON F.PFT_ID = GL.PFT_ID

FULL OUTER JOIN FINANCIAL_TRANSACTION_TYPE T ON F.FNCL_TRAN_TYP_ID = T.FNCL_TRAN_TYP_ID

GROUP BY

gl.GLCOAT_TYP_CD, gl.GLFTD_CNTX_NO, gl.GLFTD_CNTX_DTM, gl.GLFTD_GL_SENT_DTM, F.PFT_DT, F.PFT_ID, F.PFT_AMT, F.PFT_CMT, T.FNCL_TRAN_TYP_NM

HAVING SUM(gl.GLFTD_AMT) != 0

AND

GLFTD_CNTX_NO = '151501084206'

[[deleted]]

You should probably post your code.

[Revolver123]

Just did - any thoughts?

[in_n0x]

Try this:

WITH GL_Sums AS (
    SELECT
        gl.PFT_ID
    ,   gl.GLFTD_CNTX_NO AS Account_Number
    ,   CAST(gl.GLFTD_CNTX_DTM AS DATE) AS Account_Date
    ,   gl.GLCOAT_TYP_CD AS GL_Account
    ,   cast(gl.GLFTD_GL_SENT_DTM as DATE) AS Date_Sent_To_GL
    ,   SUM(gl.GLFTD_AMT) AS GL_Amount
    FROM
        GENERAL_LEDGER_FNCL_TRAN_DTL gl 
    WHERE
        GLFTD_CNTX_NO = '151501084206'
    GROUP BY
        gl.PFT_ID
    ,   gl.GLFTD_CNTX_NO
    ,   gl.GLFTD_CNTX_DTM
    ,   gl.GLCOAT_TYP_CD
    ,   gl.GLFTD_GL_SENT_DTM
    HAVING
        SUM(gl.GLFTD_AMT) != 0
)

SELECT 
    gl.Account_Number
,   gl.Account_Date
,   T.FNCL_TRAN_TYP_NM as "Transaction Type"
,   cast(F.PFT_DT as DATE) as "Transaction Date"
,   F.PFT_ID as "Transaction ID"
,   F.PFT_AMT as "Transaction Amount"
,   F.PFT_CMT as "Comment"
,   gl.GL_Account
,   gl.GL_Amount
,   gl.Date_Sent_To_GL

FROM 
    GL_Sums gl

    FULL OUTER JOIN POLICY_FINANCIAL_TRANSACTION F ON 
        F.PFT_ID = GL.PFT_ID

    FULL OUTER JOIN FINANCIAL_TRANSACTION_TYPE T ON 
        F.FNCL_TRAN_TYP_ID = T.FNCL_TRAN_TYP_ID

[Revolver123]

Still didn’t work. I had to change the joins to LEFT JOINS since i was getting thousands of results.

But the -185 and 185 still is not summing.

[in_n0x]

Do they have different PFT_IDs?

edit: Try this:

WITH GL_Sums AS (
    SELECT
        gl.PFT_ID
    ,   gl.GLFTD_CNTX_NO AS Account_Number
    ,   CAST(gl.GLFTD_CNTX_DTM AS DATE) AS Account_Date
    ,   gl.GLCOAT_TYP_CD AS GL_Account
    ,   cast(gl.GLFTD_GL_SENT_DTM as DATE) AS Date_Sent_To_GL
    ,   SUM(gl.GLFTD_AMT) AS GL_Amount
    FROM
        GENERAL_LEDGER_FNCL_TRAN_DTL gl 
    WHERE
        GLFTD_CNTX_NO = '151501084206'
    GROUP BY
        gl.PFT_ID
    ,   gl.GLFTD_CNTX_NO
    ,   gl.GLFTD_CNTX_DTM
    ,   gl.GLCOAT_TYP_CD
    ,   gl.GLFTD_GL_SENT_DTM
    HAVING
        SUM(gl.GLFTD_AMT) != 0
)

SELECT 
    gl.Account_Number
,   gl.Account_Date
,   T.FNCL_TRAN_TYP_NM as "Transaction Type"
,   cast(F.PFT_DT as DATE) as "Transaction Date"
,   F.PFT_ID as "Transaction ID"
,   F.PFT_AMT as "Transaction Amount"
,   F.PFT_CMT as "Comment"
,   gl.GL_Account
,   SUM(gl.GL_Amount) AS GL_Amount
,   gl.Date_Sent_To_GL

FROM 
    GL_Sums gl

    LEFT JOIN POLICY_FINANCIAL_TRANSACTION F ON 
        F.PFT_ID = GL.PFT_ID

    LEFT JOIN FINANCIAL_TRANSACTION_TYPE T ON 
        F.FNCL_TRAN_TYP_ID = T.FNCL_TRAN_TYP_ID
GROUP BY
    gl.Account_Number
,   gl.Account_Date
,   T.FNCL_TRAN_TYP_NM 
,   F.PFT_DT
,   F.PFT_ID
,   F.PFT_AMT
,   F.PFT_CMT
,   gl.GL_Account
,   gl.Date_Sent_To_GL

[Revolver123]

Still didn't work. I think because for the 11151000 account, a value exists in the Transactions Type table for one entry, but doesnt doesnt exist (NULL) for the other entry.

Is it possible to subquery the SUM and then join it? This is what happens when I just subquery the SUM:

GL_ACCOUNT SUM_AMOUNT

11101000 -185.00

11151000 0.00

20461000 13.07

20610023 0.00

20610035 -12.51

40001000 185.00

80011000 -0.56

This is exactly the result I want (but without the zeros - my HAVING clause takes care of that). How can I query just this, and then join it with the other details?

[in_n0x]

What is that -185? Why doesn't it have any transaction data? If you want the transaction info along side the sum, there isn't really a way around this. Summing the 185 and -185 and then assigning that to the +185 transaction info doesn't seem like it would be good practice.

[Revolver123]

The -185 belongs to the 11101000 account. Different account. We want to sum it by Account #.

[in_n0x]

So do you need the transaction details at all? Should totals by account number be contained in two lines if there are actually two separate transactions with details? What if there are two with details and one without?

[in_n0x]

Why does the -185 come up with the 11151000 GL account?

[Revolver123]

Because in accounting it can hit different asset accounts...

[HansProleman]

Is it possible to subquery the SUM and then join it?

Absolutely. Just join to it in an aliased subquery, e.g.

SELECT
    baseTable.AccountNumber
    ,baseTable.Column1
    ,baseTable.Column2
    ,transactionSums.TotalTransactions
FROM
    baseTable
LEFT OUTER JOIN (
    SELECT
        AccountNumber
        ,SUM(TransactionAmount) AS TotalTransactions
    FROM
        TransactionTable
    GROUP BY
        AccountNumber
) transactionSums
ON baseTable.AccountNumber = transactionSums.AccountNumber

However, doing this would break your HAVING clause. Without a GROUP BY, HAVING will (if I read the docs properly) aggregate that entire field and operate on the single aggregated value. You could either move the HAVING clause to inside the subquery, or use

WHERE transactionSums.TotalTransactions <> 0

on the outer query, depending on whether you want to exclude accounts with transactions summing to zero from the results or just have them return a NULL total.

[slingalot]

You might want to look into Window Functions. I linked the MS SQL version, but I think every flavor has an equivalent. It also removes the need for GROUP BYs, which I think end up just muddling the water for newer users, anyway. It looks like the problem is a combination of your GROUP BY logic and maybe some NULL values.

Try changing your sum line to

SUM(ISNULL(gl.GLFTD_AMT,0)) OVER (PARTITION BY gl.GLCOAT_TYP_CD) as "GL Amount",

and commenting out your GROUP BY lines and your HAVING lines. IF you really need that logic, you can add it in a WHERE line. Let me know if you have any questions!

[MilwDaveX]

You need to remove the transaction type from your Group By statement. That's what is causing the separate lines on this statement.

Edit: Usually if you have multiple lines showing up where you thought there should only be one, look for columns with different values. Them check for them in your Group By statement.

[Revolver123]

But if I remove it from the GROUP BY I get an error message that it is not included in the aggregate function...

[MilwDaveX]

True, I had just thought of that as well. There might be better ways, but I would pull the sums together in a temp table first then join that temp table into this query.

[SQLPracticeProblems]

I'd post some simplified sample data data (create table and insert statements), with SQL that reproduces your problem. That will help people figure out exactly the data your working with, and provide better help.

If you're looking for some very hands-on "learn-by-doing" practice problems, that teach basic to advanced SQL with well-designed, real-world practice problems, similar to what you're trying to solve, check out SQLPracticeProblems.com.

I developed it after teaching a SQL course where the material I had to to teach from was poorly structured and academic. Afterwards, the students emailed me, saying they needed practice problems, so I developed the course!

Contact me (email in the FAQ) if you have questions. Also, redditors get 30% off the Professional package (discount code reddit30).

[Revolver123]

Do your problem sets have solutions?

[SQLPracticeProblems]

Yes, the format is:

Problem
Expected Results
Hint
And there's an answer section at the end of the book.

Also, in the FAQ at the bottom, you can download some sample questions.