When each ticket can have several line items, count distinct ticket IDs and sum the line amounts at the same grouping level. Conditional aggregation can place selected terminals into columns without building separate temporary tables.
Last updated: October 8, 2026.
SELECT CAST(s.SaleDate AS date) AS SaleDay,
COUNT(DISTINCT s.TicketId) AS Tickets,
SUM(s.Amount) AS Sales,
COUNT(DISTINCT CASE WHEN s.TerminalId = 1 THEN s.TicketId END)
AS POS1Tickets,
SUM(CASE WHEN s.TerminalId = 1 THEN s.Amount ELSE 0 END)
AS POS1Sales,
COUNT(DISTINCT CASE WHEN s.TerminalId = 2 THEN s.TicketId END)
AS POS2Tickets,
SUM(CASE WHEN s.TerminalId = 2 THEN s.Amount ELSE 0 END)
AS POS2Sales
FROM dbo.Sales AS s
WHERE s.SaleDate >= @FromDate
AND s.SaleDate < DATEADD(day, 1, @ThroughDate)
GROUP BY CAST(s.SaleDate AS date)
ORDER BY SaleDay;The half-open date range includes every time on @ThroughDate without applying a function to the filtered column. That gives an index on SaleDate a better chance to support the range seek.
Match the aggregates to the table grain
If one row represents one ticket, plain COUNT(*) is enough. If one row represents a ticket line, COUNT(DISTINCT TicketId) avoids counting the same ticket repeatedly. Confirm whether Amount is a line amount or a duplicated ticket total; summing a repeated ticket total would overstate sales.
Microsoft documents that SUM adds numeric expressions and ignores nulls. The searched CASE expression returns a value only for rows matching each terminal condition.
Return rows instead of columns for changing terminals
Hard-coded POS columns work for a fixed dashboard. When terminals are added frequently, group by both date and TerminalId and let the reporting layer render the matrix. Dynamic SQL can generate changing columns, but it is more complex to parameterize, test, and consume.
Use decimal for money calculations, decide how refunds affect the total, and test dates around local midnight. If timestamps are stored in UTC, convert to the business time zone before deriving the sales day. Continue with SQL aggregate functions, grouping SQL rows, and filtering rows with SELECT.
