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

Output

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 :-)

asked 08 Sep '17, 16:01

gchq's gravatar image

gchq
421263141
accept rate: 27%

edited 10 Sep '17, 07:44

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819


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')
   

permanent link

answered 10 Sep '17, 07:43

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 11 Sep '17, 01:15

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

(10 Sep '17, 07:44) Volker Barth

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()
permanent link

answered 08 Sep '17, 18:42

gchq's gravatar image

gchq
421263141
accept rate: 27%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×243
×11

question asked: 08 Sep '17, 16:01

question was seen: 1,576 times

last updated: 11 Sep '17, 01:15