sql server - Inner join query logic

Question

I am using Sql Server Management studio.

I have the following table.

when i run the following query I get the running total for sales column

select s1.date,
sum(s2.sales)
from sales s1
join sales s2 on s1.date>=s2.date
group by s1.date;

but when i substitute s2.sales with s1.sales in the select

select s1.date,
**sum(s1.sales)**
from sales s1
join sales s2 on s1.date>=s2.date
group by s1.date;

it gives me a different answer can someone help me understand why i am facing this? since the sales column value should be the same.

question from:https://stackoverflow.com/questions/66057945/inner-join-query-logic

Answer 1

The first version of your running total query is summing sales, for each dates, over dates which are strictly less than or equal to the date in each record. When you change s2.sales to s1.sales, you are then summing the current record's sales N number of times, where N is the number of records having an earlier date. This clearly is not the logic you want, so stick with the first version.

By the way, if you're using MySQL 8+, then analytic functions simplify things even further:

SELECT Date, Sales, SUM(Sales) OVER (ORDER BY Date) RunningSales
FROM sales
ORDER BY Date;