CODEWITHSUNDEEP
sql
William
William

Reputation: 61

Select from two tables and sum

i have a little question in my syntax please help me.

Table TransM
Code_Project   Code_Name  Code_Stat   Project_Name
17-0004        A          1           ABC  
17-0004        A          2           ABC   
17-0004        B          1           DEF 

Tabel TransW
Code_Project     Code_Name      Total
17-0004          A              1000
17-0004          A              1500
17-0004          A              1200
17-0004          B              1600

The Result i want is like this :

Code_Project   Code_Name   Project_Name   GrandTotal
17-0004        A           ABC            3700
17-0004        B           DEF            1600

i use this syntax and the grandtotal is wrong

SELECT
  a.Code_Project,
  a.Code_Name,
  a.Project_Name,
  SUM(b.Total) AS GrandTotal
FROM
  TransM a
  INNER JOIN TransW b
    ON a.Code_Project = b.Code_Project
    AND a.Code_Name = b.Code_Name
WHERE a.Code_Project = '17-0004'
GROUP BY a.Code_Project,
  a.Code_Name,
  a.Project_Name

Please tell me the right syntax, thanks

Upvotes: 1

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can also do this with union all and group by:

select Code_Project, Code_Name,
       max(Project_Name) as Project_Name, 
       sum(Total) as GrandTotal
from ((select Code_Project, Code_Name, Project_Name, 0 as Total
       from TransM
      ) union all
      (select Code_Project, Code_Name, NULL Project_Name, Total
       from TransW
      ) 
     ) mw
group by Code_Project, Code_Name;

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

You have multiple rows in TransM matching multiple rows in TransW because the Code_Project, Code_Name, Project_Name are not unique in TransM.

You could get the distinct set as a subquery/derived table and join using that like so:

select 
    a.Code_Project
  , a.Code_Name
  , a.Project_Name
  , sum(b.Total) as GrandTotal
from (select distinct Code_Project, Code_Name, Project_Name from TransM) a
  inner join TransW b
    on a.Code_Project = b.Code_Project
   and a.Code_Name = b.Code_Name
where a.Code_Project = '17-0004'
group by 
    a.Code_Project
  , a.Code_Name
  , a.Project_Name

If there are multiple distinct values for Project_Name for a given Code_Project, Code_Name then you will still get duplicates. In that case you would need to determine how to choose which Project_Name to return, or use min() or max() just to pick one like so:

select 
    a.Code_Project
  , a.Code_Name
  , a.Project_Name
  , sum(b.Total) as GrandTotal
from (
    select m.Code_Project, m.Code_Name, max(m.Project_Name) as Project_Name
    from TransM m
    group by m.Code_Project, m.Code_Name
    ) a
  inner join TransW b
    on a.Code_Project = b.Code_Project
   and a.Code_Name = b.Code_Name
where a.Code_Project = '17-0004'
group by 
    a.Code_Project
  , a.Code_Name
  , a.Project_Name

rextester demo: http://rextester.com/RRGFQM14769

returns: 

+--------------+-----------+--------------+------------+
| Code_Project | Code_Name | Project_Name | GrandTotal |
+--------------+-----------+--------------+------------+
| 17-0004      | A         | ABC          |       3700 |
| 17-0004      | B         | DEF          |       1600 |
+--------------+-----------+--------------+------------+

Upvotes: 1

Related Questions