CODEWITHSUNDEEP
sql-server
Saad Hamid
Saad Hamid

Reputation: 31

Joining two tables having duplicate data in both columns on the base of which we are joining

I have two tables. A column named CardName is in first table. There is duplicate data in this columns. That column also exists in second table. There is a column named amount related to each cardName also in second table. What i want is to select distinct CardName from 1st table and and take sum of all the amounts from second column whose cardname is in first table. BUT first table cardname should be distinct. what should i do?

Upvotes: 2

Views: 72

Answers (1)

backtrack
backtrack

Reputation: 8144

select name,sum(amount) from tableB 
where name in (select distinct name from TableA) 
group by name

use distinct keyword. Distinct will give you only the unique name from TableA and from the sub query result we are getting name and sum from tableB

Refer this : http://technet.microsoft.com/en-us/library/ms187831(v=sql.105).aspx

From you comment below UPDATE

with cte (name) as 
(
select distict name from TableA 

)
select cte.name,ISNULL(sum(count),0) from TableB as B 
left join cte.name = B.name

Upvotes: 1

Related Questions