When a detail list shows five records and the summary above it says six, the user has to decide which part of the screen to trust. The problem is often the input set rather than SUM itself. Reporting code in my archive combines searches, detail counts and grouped totals; the first design decision is to define exactly which rows belong to the report.
Define the unit being summarized
The example has A amounts of ten and twenty and a B amount of thirty. The active filter accepts amounts of at least twenty. The expected summary is therefore one A row totaling twenty and one B row totaling thirty. Showing thirty as A's total beside that filtered detail would require a separate label explaining that it represents an unfiltered total.
WITH Filtered AS (
SELECT Category, Amount FROM (VALUES
('A',10),('A',20),('B',30)) AS d(Category,Amount)
WHERE Amount >= 20
)
SELECT Category, COUNT(*) AS ItemCount, SUM(Amount) AS Total
FROM Filtered GROUP BY Category ORDER BY Category;This self-contained T-SQL query uses VALUES and does not require application tables. Filtered names the accepted input; GROUP BY reduces it to one row per category. COUNT(*) counts the filtered rows. The CTE communicates the boundary, but it is not a promise that the result is cached or that the query runs faster.
WHERE and HAVING express different rules
WHERE Amount >= 20 removes rows before grouping. HAVING SUM(Amount) >= 20 filters groups after their totals have been calculated. Keeping both A rows because their combined amount is thirty is different from excluding the ten before aggregation. Describing a requirement as either a row rule or a group rule prevents that ambiguity before SQL is written.
I prefer a shared query definition for filtering rather than copying the predicates into several screens. A CTE exists only for the immediately following statement; a later independent query cannot reuse its name. Separate detail and summary commands need a mechanism such as a common view, query builder or transaction-local materialized input to keep their selection rules aligned.
Join multiplicity can change the question
Joining one application to three attached documents can produce three rows for that application. COUNT(*) would then count joined rows, not applications. Summing an amount stored on the parent can multiply it as well. Define the grain of the report first, and aggregate the child side or use EXISTS where the requirement only asks whether a related row exists. DISTINCT is not a universal repair for accidental row multiplication.
Nulls have a similar semantic effect. COUNT(*) counts rows; COUNT(Amount) counts populated amounts. SUM ignores null inputs. Deciding to display zero when no suitable value exists is a separate policy. If unknown amounts and actual zero amounts mean different things, adding COALESCE without discussing that distinction can produce a misleading report.
Matching predicates do not guarantee the same read snapshot
Another writer can change data between two otherwise identical read operations. A report that requires one consistent point in time should use the database's appropriate isolation strategy. SQL Server snapshot options have environment requirements, for example. Merely opening a transaction does not guarantee that every isolation level pins all reads to one fixed snapshot.
Start with an outcome small enough to calculate manually
The expected rows here are A/1/20 and B/1/30. Extend that input with a null amount, a filter matching nothing and a join that duplicates a parent. For performance on real tables, inspect the execution plan and relevant filter and relationship indexes. Naming a CTE or choosing a particular GROUP BY spelling is not evidence of a measured speed improvement.