[SQL] SQL Server, manual sum of each row of query result different than SQL built in SUM...

I have a a db that contains data about the sales and referrals of a company.

The query to obtain the desired report wich is (Each client, its referral, and the total amount sold on a specific period of time of that client) is the next one:

SELECT DISTINCT r.phoneno, b.amount
FROM cootelcuboparque.referidos r
INNER JOIN (
SELECT phoneno, SUM(amount) AS amount
FROM cootelcuboparque.cr_history
WHERE RechargeDate BETWEEN '2024-09-01' AND '2024-09-30'
GROUP BY phoneno
) AS b ON b.phoneno = r.phoneno
INNER JOIN (
SELECT COOLTELPHONE, USERNAME
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY COOLTELPHONE ORDER BY ActiveTime ASC) rn, *
FROM cootelcuboparque.activeusers_history
) a
WHERE rn = 1
) AS c ON c.COOLTELPHONE = r.phoneno


But it gives a huge total amount of 4 million, etc...

But if just make that a subquery and get the total sum:

SELECT SUM(final_query.amount) as TotalMonto
FROM (
SELECT DISTINCT r.phoneno, b.amount
FROM cootelcuboparque.referidos r
INNER JOIN (
SELECT phoneno, SUM(amount) AS amount
FROM cootelcuboparque.cr_history
WHERE RechargeDate BETWEEN '2024-09-01' AND '2024-09-30'
GROUP BY phoneno
) AS b ON b.phoneno = r.phoneno
INNER JOIN (
SELECT COOLTELPHONE, USERNAME
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY COOLTELPHONE ORDER BY ActiveTime ASC) rn, *
FROM cootelcuboparque.activeusers_history
) a
WHERE rn = 1
) AS c ON c.COOLTELPHONE = r.phoneno
) AS final_query;


I get the desired value:
Wich is 200 k aproximately...

The problem is i need the correct individual values for the report.

What could be changing the way things are summed?

I even told chatgpt to build a funcion that instead of just suming the whole thing with the built in sum, to better select the data and then iterate over it and sum it, and it gives the expected value of 200 k...

i know the 200 k is right because on a web app there is a simpler report that only gets the total amount sold for the period of time and it is the same 200 k.

Thanks!

Continue reading...