SQL Summing a column value based conditional on another table's value

Question

I'm working on a SQL query that returns a SUM of a value in a column/table based off a related value in another table's column being met. I'm getting an error:

The multi-part identifier "Charges.JR_LocalSEllAmt" could not be bound. The multi-part identifier "Filtered.ChargeFAF" could not be bound.

Are you able to see anything clearly wrong with my code below? I'm unsure about how to access the AccChargeCode tables column values to check the condition before returning a SUM from a column within the Job Charge table.

SELECT  Charges.Filtered.ChargeFAF AS ChargeFAF
LEFT JOIN dbo.JobHeader ON LTC_PK = JH_ParentID
LEFT JOIN dbo.JobCharge ON JH_PK = JR_JH AND JR_OSSellAmt > 0
LEFT JOIN dbo.AccChargeCode ON JR_AC = AC_PK
LEFT JOIN 
    (
        SELECT
            JR_JH,
            JR_LocalSellAmt,
            JR_AC
        FROM
            dbo.JobCharge
        LEFT JOIN
        (
            SELECT
            AC_PK,
            AC_Code,
            SUM(CASE WHEN AC_Code = 'LTFAF' THEN Charges.JR_LocalSellAmt ELSE 0 END) AS ChargeFAF       
            FROM
                dbo.AccChargeCode
            GROUP BY
                AC_PK,
                AC_Code
        ) AS Filtered ON Filtered.AC_PK = JR_AC
    GROUP BY
        JR_JH
)  AS Charges ON Charges.JR_JH = JH_PK

What database system and version? – J.D. Sep 21 '22 at 12:13 — J.D., Sep 21 '22 at 12:13

score 1 · Answer 1 · answered Sep 21 '22 at 12:56

This should do it. See code for comments.

SELECT 
    ChargeFAF = 
        Charges.ChargeFAF /* Remove .Filter */
FROM dbo.SomeTable /* You were missing a FROM here */
LEFT JOIN dbo.JobHeader 
    ON LTC_PK = JH_ParentID
LEFT JOIN dbo.JobCharge 
    ON  JH_PK = JR_JH 
    AND JR_OSSellAmt > 0
LEFT JOIN dbo.AccChargeCode 
    ON JR_AC = AC_PK
LEFT JOIN 
(
    SELECT
        JR_JH,
        JR_LocalSellAmt,
        JR_AC,
        ChargeFAF 
        /* You need to select this 
           column here to reference 
           it in the outer SELECT */
    FROM dbo.JobCharge        
    LEFT JOIN
    (
        SELECT
        AC_PK,
        AC_Code,
        ChargeFAF = 
            SUM
            (
                CASE 
                    WHEN AC_Code = 'LTFAF' 
                    THEN Charges.JR_LocalSellAmt 
                    ELSE 0 
                END
            )       
        FROM dbo.AccChargeCode
        GROUP BY
            AC_PK,
            AC_Code
    ) AS Filtered 
      ON Filtered.AC_PK = JR_AC
    GROUP BY
        JR_JH
)  AS Charges 
   ON Charges.JR_JH = JH_PK;

SQL Summing a column value based conditional on another table's value

1 Answers1