CODEWITHSUNDEEP
sqlsql-servert-sqlcount
user3967893
user3967893

Reputation: 5

Count unique rows of 3 join tables

I would like to count several items in the 3 tables:

Sample data below:

Table Test1
CallNr      CallType  
1           rej  
2           rej  
3           rej  
4           rej  
5           QC  
6           QC  
7           rej  
8           rej  

Table Test2
CallNr     Code  
1          201401  
3          201402  
4          201404  
5          201401  
7          201401  
8          201401

Table Test3  
Code    PartCode  SerialNumber  
201401  68001     123-01  
201402  68001     123-02  
201403  68001     123-03  
201404  68001     124-01  
201405  68001     124-02  
201406  68001     124-03

The result must be:

Batch Total NoCall Call_Per_Code  
123   3     1      2  
124   3     2      1

But I get the result: 

Batch Total NoCall Call_Per_Code  
123   3     1      4  
124   2     1      1

I am using the sql code on a SQL Server 2012:

SELECT
   substring(T3.SerialNumber,1,3) as batch
   ,COUNT(DISTINCT concat(T3.code,substring(T3.SerialNumber,1,3))) as Total
   ,sum(case when T2.CallNr is null then 1 else 0 end) as NoCall
   ,sum(case when T2.CallNr is null then 0 else 1 end) as Call_per_Code
FROM Test1 T1 
    INNER JOIN Test2 T2 
        ON T1.CallNr=T2.CallNr AND T1.CallType='rej' 
    RIGHT JOIN Test3 T3 
        ON T2.Code=T3.Code  
GROUP BY substring(T3.SerialNumber,1,3)

How can I get the correct count on the column Call_Per_Code. The Code 201401 with a CallNr is count as 3 instead of 1.

Upvotes: 0

Views: 79

Answers (1)

Jordan Parker
Jordan Parker

Reputation: 1236

This query gets a lot easier if you start from Test3 - you don't even need to join to Test1 if you start from there:

SELECT
   SUBSTRING(T3.SerialNumber, 1, 3) AS Batch,
   COUNT(DISTINCT T3.Code) AS Total,
   SUM(CASE WHEN T2.CallNr IS NULL THEN 1 ELSE 0 END) AS NoCall,
   COUNT(DISTINCT T2.Code) AS Call_Per_Code
FROM Test3 T3
    LEFT OUTER JOIN Test2 T2 ON T3.Code = T2.Code
GROUP BY SUBSTRING(T3.SerialNumber, 1, 3)

Upvotes: 1

Related Questions