sql sum data from multiple tables

by

The results from your query are wrong because the values you are trying to summarize are being grouped, which causes duplicate values to be included in the SUM.

You could solve it with a couple of sub-selects:

SELECT 
    AP1.[PROJECT],
    (SELECT SUM(AP2.Value) FROM AP AS AP2 WHERE AP2.PROJECT = AP1.PROJECT) AS SUM_AP,
    (SELECT SUM(INV2.Value) FROM INV AS INV2 WHERE INV2.PROJECT = AP1.PROJECT) AS SUM_INV
FROM AP AS AP1 
    INNER JOIN INV AS INV1 
        ON (AP1.[PROJECT] =INV1.[PROJECT])
WHERE AP1.[PROJECT] = 'XXXXX'
GROUP BY AP1.[PROJECT]

More Related Contents:

  1. What is the result of following SQL Query?
  2. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  3. Explicit vs implicit SQL joins
  4. Join vs. sub-query
  5. Can I use CASE statement in a JOIN condition?
  6. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  7. SQL join: selecting the last records in a one-to-many relationship
  8. Subqueries vs joins
  9. Does the join order matter in SQL?
  10. Difference between left join and right join in SQL Server [duplicate]
  11. What is the difference between JOIN and UNION?
  12. Filter Table Before Applying Left Join
  13. SQL Join Differences
  14. Difference between natural join and inner join
  15. Are “from Table1 left join Table2” and “from Table2 right join Table1” interchangeable?
  16. Inner Join vs Natural Join vs USING clause: are there any advantages?
  17. Natural join in SQL Server
  18. Including null values in an Apache Spark Join
  19. Is a JOIN faster than a WHERE?
  20. Select only rows by join tables max value
  21. Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?
  22. What is semi-join in database?
  23. MySQL: GROUP_CONCAT with LEFT JOIN
  24. Merge two rows in SQL
  25. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  26. Venn Diagram for Natural Join
  27. Why do multiple-table joins produce duplicate rows?
  28. SQL server join tables and pivot
  29. IN vs. JOIN with large rowsets
  30. SQL join on multiple columns in same tables

Leave a Comment