Trying to use INNER JOIN and GROUP BY SQL with SUM Function Not Working

Question

I am not getting my head around this  and wondered if anyone may be able to help me with this   I have 2 Tables called RES DATA and INV DATA  RES DATA Contains my Customer as below  CUSTOMER ID   NAME  1  Robert 2  John 3  Peter   INV DATA Contains their INVOICES as Below  INVOICE ID   CUSTOMER ID   AMOUNT  100  1    49 95 200  1    105 95 300  2    400 00 400  3    150 00 500  1    25 00   I am Trying to write a SELECT STATEMENT Which will give me the results as Below   CUSTOMER ID   NAME   TOTAL AMOUNT  1  Robert    180 90 2  John    400 00 3  Peter    150 00   I think I need 2 INNER JOINS Somehow to Add the tables and SUM Values of the INVOICES Table GROUPED BY the Customer Table but honestly think I am missing something  Can t even get close to the Results I need

User · Answer

Use subquery  SELECT   FROM RES DATA inner join  SELECT  CUSTOMER ID   sum  TOTAL AMOUNT   FROM INV DATA group by  CUSTOMER ID   T on RES DATA  CUSTOMER ID    t  CUSTOMER ID

User · Answer

If you need to retrieve more columns other than columns which are in group by then you can consider below query  Check it once whether it is performing well or not    SELECT  a  CUSTOMER ID    a  NAME     select SUM b  AMOUNT   from INV DATA b where b  CUSTOMER ID    a  CUSTOMER ID  GROUP BY b  CUSTOMER ID   AS  TOTAL AMOUNT  FROM RES DATA a

User · Answer

This should work   SELECT a  CUSTOMER ID   a  NAME   SUM b  AMOUNT   AS  TOTAL AMOUNT  FROM RES DATA a INNER JOIN INV DATA b ON a  CUSTOMER ID  b  CUSTOMER ID  GROUP BY a  CUSTOMER ID   a  NAME    I tested it with SQL Fiddle against SQL Server 2008  http   sqlfiddle com   3 1cad5 1  Basically what s happening here is that  because of the join  you are getting the same row on the  left   i e  from the RES DATA table  for every row on the  right   i e  the INV DATA table  that has the same  CUSTOMER ID  value   When you group by just the columns on the left side  and then do a sum of just the  AMOUNT  column from the right side  it keeps the one row intact from the left side  and sums up the matching values from the right side

User · Answer

Two ways to do it     GROUP BY  SELECT RES  CUSTOMER ID   RES NAME  SUM INV AMOUNT  AS  TOTAL AMOUNT  FROM RES DATA RES JOIN INV DATA INV ON RES  CUSTOMER ID  INV  CUSTOMER ID  GROUP BY RES  CUSTOMER ID   RES NAME   OVER  SELECT RES  CUSTOMER ID   RES NAME          SUM INV AMOUNT  OVER  PARTITION RES  CUSTOMER ID   AS  TOTAL AMOUNT  FROM RES DATA RES JOIN INV DATA INV ON RES  CUSTOMER ID  INV  CUSTOMER ID

[sql] Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

Examples related to sql

Examples related to select

Examples related to join

Examples related to group-by

Examples related to inner-join