5

I am using following Query to get the SUM of field Amount of all lines in my filter:

SELECT SUM(Amount)
FROM dbo.[CompanyName$Detailed Cust_ Ledg_ Entry]
WHERE [Customer No_] = 'XYZ'

The result is

Resukt

This result doesn't add up to the sum of lines

SELECT [Customer No_], Amount
FROM dbo.[CompanyName$Detailed Cust_ Ledg_ Entry]
WHERE [Customer No_] = 'XYZ'

Result of lines is:

Lines Result

When calculating alle amounts of lines the result is: -29,59 and not -59,18

Can somebody explain this behaviour?

SP_Helpindex Output: SP_Helpindex Output

QueryPlan: Query Plan

XML QueryPlan

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.481" Build="14.0.3045.24" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="9.00002" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.029753" StatementText="SELECT 'XYZ' [Customer No_],[Amount] FROM [dbo].[CompanyName$Detailed Cust_ Ledg_ Entry] WHERE [Customer No_]=@1" StatementType="SELECT" QueryHash="0x97A8CCC9F15EC998" QueryPlanHash="0x7502550BCACA55B0" RetrievedFromCache="false" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="552">
            <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="0" MaxQueryMemory="8063784" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1363148" EstimatedPagesCached="340787" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="34964752" />
            <OptimizerStatsUsage>
              <StatisticsInfo Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Statistics="[$8]" ModificationCount="25" SamplingPercent="100" LastUpdate="2020-02-10T06:55:52.34" />
              <StatisticsInfo Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Statistics="[$1]" ModificationCount="18" SamplingPercent="100" LastUpdate="2020-02-10T06:55:47.85" />
              <StatisticsInfo Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Statistics="[$11]" ModificationCount="18" SamplingPercent="100" LastUpdate="2020-02-10T06:57:02.26" />
              <StatisticsInfo Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Statistics="[$6]" ModificationCount="18" SamplingPercent="100" LastUpdate="2020-02-10T06:55:59.59" />
              <StatisticsInfo Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Statistics="[$10]" ModificationCount="18" SamplingPercent="100" LastUpdate="2020-02-10T06:56:54.58" />
              <StatisticsInfo Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Statistics="[$4]" ModificationCount="18" SamplingPercent="100" LastUpdate="2020-02-10T06:56:09.84" />
              <StatisticsInfo Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Statistics="[$9]" ModificationCount="25" SamplingPercent="100" LastUpdate="2020-02-10T06:56:46.87" />
              <StatisticsInfo Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Statistics="[$7]" ModificationCount="18" SamplingPercent="100" LastUpdate="2020-02-10T06:56:35.44" />
              <StatisticsInfo Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Statistics="[$5]" ModificationCount="18" SamplingPercent="100" LastUpdate="2020-02-10T06:56:31.71" />
            </OptimizerStatsUsage>
            <TraceFlags IsCompileTime="true">
              <TraceFlag Value="3226" Scope="Global" />
              <TraceFlag Value="4199" Scope="Global" />
            </TraceFlags>
            <TraceFlags IsCompileTime="false">
              <TraceFlag Value="3226" Scope="Global" />
              <TraceFlag Value="4199" Scope="Global" />
            </TraceFlags>
            <QueryTimeStats CpuTime="1" ElapsedTime="1" />
            <RelOp AvgRowSize="29" EstimateCPU="9.00002E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="9.00002" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.029753">
              <OutputList>
                <ColumnReference Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Column="Amount" />
                <ColumnReference Column="Expr1002" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1002" />
                    <ScalarOperator ScalarString="'XYZ'">
                      <Const ConstValue="'XYZ'" />
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="24" EstimateCPU="3.76201E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="9.00002" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0297521">
                  <OutputList>
                    <ColumnReference Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Column="Amount" />
                  </OutputList>
                  <MemoryFractions Input="0" Output="1" />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="12" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="true">
                    <OuterReferences>
                      <ColumnReference Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Column="Entry No_" />
                    </OuterReferences>
                    <RelOp AvgRowSize="11" EstimateCPU="0.0001669" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="9.00002" EstimatedRowsRead="9.00002" LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032919" TableCardinality="5304040">
                      <OutputList>
                        <ColumnReference Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Column="Entry No_" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="12" ActualRowsRead="12" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Column="Entry No_" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Index="[$5]" IndexKind="NonClustered" Storage="RowStore" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Column="Customer No_" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="N'XYZ'">
                                    <Const ConstValue="N'XYZ'" />
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="24" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.00002" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0264226" TableCardinality="5304040">
                      <OutputList>
                        <ColumnReference Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Column="Amount" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="12" ActualRowsRead="12" Batches="0" ActualEndOfScans="0" ActualExecutions="12" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="36" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Column="Amount" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Index="[CompanyName$Detailed Cust_ Ledg_ Entry$0]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Column="Entry No_" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[DBName].[dbo].[CompanyName$Detailed Cust_ Ledg_ Entry].[Entry No_]">
                                    <Identifier>
                                      <ColumnReference Database="[DBName]" Schema="[dbo]" Table="[CompanyName$Detailed Cust_ Ledg_ Entry]" Column="Entry No_" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </ComputeScalar>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@1" ParameterDataType="varchar(8000)" ParameterCompiledValue="'XYZ'" ParameterRuntimeValue="'XYZ'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Query Plan (SUM): https://pastebin.com/T5xqxVg8

enter image description here

View Definition

CREATE VIEW [dbo].[CompanyName$Detailed Cust_ Ledg_ Entry$VSIFT$5] 
WITH SCHEMABINDING 
AS 
SELECT "Customer No_","Currency Code","Initial Entry Global Dim_ 1","Initial Entry Global Dim_ 2","Initial Entry Due Date","Posting Date",COUNT_BIG(*) AS "$Cnt",SUM("Amount") AS "SUM$Amount",SUM("Amount (LCY)") AS "SUM$Amount (LCY)" 
FROM dbo."CompanyName$Detailed Cust_ Ledg_ Entry" 
GROUP BY "Customer No_","Currency Code","Initial Entry Global Dim_ 1","Initial Entry Global Dim_ 2","Initial Entry Due Date","Posting Date"
0
2

Thanks to @JoshDarnell and @TonyHinkle. After deleting all connected views and recreating them with the correct index, the correct amount was shown.

In case of Dynamics NAV 2017 Detailed Customer Ledger Entry Table, there were three views WITH SCHEMABINDING:

SIFT Views of table

Export the CREATE QUERY for the view and index(!) in order to recreate them.

The creation of the index could take a few seconds.

After that, the amounts of SUM and the lines are equal.

Not the answer you're looking for? Browse other questions tagged or ask your own question.