Combining some results in a GROUP

[former_SQLA_member1694879]

Using this SQL statement

SELECT A_Sales_Ledger.Nominal_Code, NL_Codes.NL_Description, SUM(A_Sales_Ledger.DEbit), SUM(A_Sales_Ledger.Credit)  FROM A_Sales_Ledger 
A_Sales_Ledger LEFT JOIN NL_Codes on A_Sales_Ledger.nominal_Code = NL_Codes.NL_Code
WHERE Customer_ID = 49
group by Nominal_Code, NL_Codes.NL_Description

Returns this

I need to refine this query so that I can add the values for 0 to the values of 4105 and remove 0 from the output...

Clearly I already have my Friday night head on as anything I try doesn't work 🙂

[VolkerBarth]

Here's a past friday night approach:

If you want to use a grouping that is not simply based on the values of one column but want to "merge" two groups into one, you can either

• modify the underlying rows before the grouping so that rows that should build the same merged group return the same values in the grouping columns, or
• use a GROUP BY with an according expression, say with an IF or CASE expression on the original rows.

I do not understand your schema but here's an attempt:

1. Adapt the rows before the grouping

In my understanding, that would mean you turn the left join into an inner join by replacing the join condition with one that joins the value 0 to 4105 and otherwise do the grouping as usually, somewhat like

   SELECT
      N.NL_Codes AS Nominal_Code,
      N.NL_Description,
      SUM(A.Debit), SUM(A.Credit)
   FROM A_Sales_Ledger A
      INNER JOIN NL_Codes N ON IF A.Nominal_Code = 0 THEN 4105 END IF = N.NL_Code
   WHERE Customer_ID = 49
   GROUP BY N.NL_Codes, N.NL_Description
   

1. Or use an expression within the grouping

Note that then the according expression must also appear in the SELECT clause, and note that a GROUP BY expression does not allow an alias, such as

   SELECT
      IF Nominal_Code = 0 THEN 4105 ELSE Nominal_Code END IF AS Nominal_Code,
      ISNULL(N.NL_Description, 'Assessments-Regular') AS NL_Description,
      SUM(A.Debit), SUM(A.Credit)
   FROM A_Sales_Ledger A
      LEFT JOIN N NL_Codes N on A.Nominal_Code = N.NL_Code
   WHERE Customer_ID = 49
   GROUP BY
      IF Nominal_Code = 0 THEN 4105 ELSE Nominal_Code END IF,
      ISNULL(N.NL_Description, 'Assessments-Regular')
   

[VolkerBarth]

Ah, the preview showed both samples numbered with 1. and 2., don't know why the real view does not.

[former_SQLA_member1694879]

In the end I just manipulated the returned data - a cleaner method would have been preferable, but in this case only a few rows are returned so no real performance impact

Dim vDebit As Decimal = 0
        Dim vCredit As Decimal = 0
        For Each Row As DataRow In MoveNominalDT.Rows
            If Row("Code") = 0 Then
                vDebit = Row("Debit")
                vCredit = Row("Credit")
            End If
        Next

        For Each Row As DataRow In MoveNominalDT.Rows
            If Row("Code") = vAssDefault Then
                Row("Credit") += vCredit
                Row("Debit") += vDebit
            End If
            Row("Balance") = Row("Debit") - Row("Credit")
        Next

        For Each Row As DataRow In MoveNominalDT.Rows
            Row("Selected") = False
            If Row("Debit") = Row("Credit") Then
                Row.Delete()
            End If
        Next

        MoveNominalDT.AcceptChanges()

        For Each Row As DataRow In MoveNominalDT.Rows
            If Row("Code") = 0 Then
                Row.Delete()
            End If
        Next

        MoveNominalDT.AcceptChanges()