CODEWITHSUNDEEP
sqlsql-serverjoincount
ultranoobkiller
ultranoobkiller

Reputation: 23

Sql Count displaying wrong data when join is used

I am new to Sql, so not sure what I am doing wrong.

I have two tables:

Data in Tbl 1 

EmpID---------PrdcType-------EmpName  
1000   -------- A -------------X1   
2000   -------- B -------------X2    
1000   -------- A -------------X1    
3000   -------- A -------------X3    
so on.....

Data in TbL 2

EmpID-------ExPrdc--------EmpName    
2000   ------ A -------------X2    
3000   ------ B -------------X3    
1000   ------ A -------------X1    
1000   ------ A -------------X1
So on...

Result should be: 

EmpName--------EmpID--------Table1_A_Prdc_Count--------Table2_A_Prdc_Count    
X1-------------1000---------2---------------------------2    
X2-------------2000---------0---------------------------1    
X3-------------3000---------1---------------------------0

So on..

But i am getting:

EmpName--------EmpID--------Table1_A_Prdc_Count--------Table2_A_Prdc_Count
X1-------------1000---------1547-----------------------------------2131
X2-------------2000---------321------------------------------------987
X3-------------3000---------46546----------------------------------3265465

These numbers are totally random, but numbers are same every time unless I change the 0 value to null or any other. I tried checking the patterns but with no use.

The code I am using: 

select r.EmpName,r.EmpID,
       count(CASE WHEN d.PrdcType='A' Then 1 else 0 END) as Table1_A_Prdc_Count,
       COUNT(CASE WHEN r.ExPrdc='A' Then 1 else 0 END) as CAL
from Tbl1 r
    INNER join Tbl2 d on r.EmpID= d.EmpID
where  r.EmpID in('X1','X2','X3') and  d.EmpID in ('X1','X2','X3')
group by r.EmpName,r.EmpID,d.EmpID,d.EmpName

And when I use same code without inner join(using it separately for different tables) I get the correct results but as soon as I add inner join it is not working. Please help me in this as I am stuck with this for more than 2 weeks. Thank you in advance.

Upvotes: 2

Views: 75

Answers (2)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

SQL DEMO

You current query have several problems, in fact it does not run. You can get your desired result by implementing a simple idea.

Count A from Tbl1 using a query and count A from Tbl2 using another query and then JOIN them like below :

select r.EmpName, r.EmpID, Table1_A_Prdc_Count, Table2_A_Prdc_Count 
from 
(
    select EmpName, EmpID, 
    sum(CASE WHEN PrdcType='A' Then 1 else 0 END) as Table1_A_Prdc_Count
    from Tbl1 d
    where d.EmpName in('X1','X2','X3')
    group by EmpName, EmpID
) as d
join 
(
    select EmpName, EmpID, 
    sum(CASE WHEN ExPrdc='A' Then 1 else 0 END) as Table2_A_Prdc_Count
    from Tbl2 
    where EmpName in('X1','X2','X3')
    group by EmpName, EmpID
) as r on d.EmpID = r.EmpID and d.EmpName = r.EmpName

OUTPUT

EmpName EmpID   Table1_A_Prdc_Count     Table2_A_Prdc_Count
-----------------------------------------------------------
X1      1000    2                       2
X2      2000    0                       1
X3      3000    1                       0

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

this does a full count not just product A
let me think about it 

select a.*, b.cnt as cntB
from ( select r.EmpName, r.EmpID, count(*) as cnt
         from Tbl1 r  
        where r.EmpID in('X1','X2','X3') 
        group by r.EmpName, r.EmpID 
     ) a
join ( select d.EmpName, d.EmpID, count(*) as cnt
         from TbL 2 d 
        where d.EmpID in('X1','X2','X3') 
        group by d.EmpName, d.EmpID 
     ) b
  on a.EmpID = b.EmpID

product A only

select isnull(a.EmpName, b.EmpName) as name
     , isnull(a.EmpID,   b.EmpID)   as id 
     , isnull(a.cnt, 0) as cntA
     , isnull(b.cnt, 0) as cntB
from ( select r.EmpName, r.EmpID, count(*) as cnt
         from Tbl1 r  
        where r.EmpID in('X1','X2','X3')  
          and PrdcType = 'A'
        group by r.EmpName, r.EmpID 
     ) a
full outer join 
     ( select d.EmpName, d.EmpID, count(*) as cnt
         from TbL 2 d 
        where d.EmpID in('X1','X2','X3') 
          and ExPrdc = 'A'
        group by d.EmpName, d.EmpID 
     ) b
  on a.EmpID = b.EmpID 
order by isnull(a.EmpID, b.EmpID)

Upvotes: 1

Related Questions