I need help using SUM and GROUP BY in SQL Server.

I am generating the query based on 5 tables. I have tried in SQL Server.

Some parts of the query are working, but when I advance the query, I get the wrong results/data.

The problem is that the data is processed twice instead of once on every group by field, e.g. farmer_ID, where the farmer bears or has two or more records. 

This happens when i add more tables to the join - on one or two tables, the sum values are okay. Hence I get farmer_sales = 200 instead of 100.

Kindly let me know how I can get some help

Thanks David   

CodePudding user response：

1. You can use the outer join (Left or Right) and choose the table that have only one record for each item

2. Another solution you can use Keyword Distinct before the column name

CodePudding user response：

Nobody here will be able to help without table definitions, the requirements & the query.
With these issues I find it helpful to write the query so you get the proper rows you need. It sounds like you have either dodgy data or incomplete join conditions but it's not possible to tell that without the above. You can debug your data by finding a problem (e.g. farmer_sales) and working through the raw data & query from there. You will have an incomplete PK/FK relationship in your query or a missing constraint allowing bad data. Or you have misunderstood the requirement or the requirement does not makes sense to the data model.

Once you have the query working correctly you can add the aggregations.

One bit of general advice I can give is that adding DISTINCT is almost always the wrong approach.