CODEWITHSUNDEEP
sqlteradata
A.K. Singh
A.K. Singh

Reputation: 45

Teradata Substract Columns of two tables in one query

I am new to teradata I have two tables suppose tbl_A and tbl_B with same schema. My Objective is to :

  1. Get the difference of count(*) of both tables when I am grouping the data on column 1,2,3

schema is as 

   Library_Card_Type Student_Class Book_Type
c1                  A           NOVEL
c2                  B           HISTORY
c3                  C           MOVIE
c4                  D           GK
c1                  E           POETRY
c1                  A           NOVEL
c2                  B           HEALTH
c3                  C           POLITICS
c4                  D           SQL
c1                  E           JAVA

I wanted to get the difference of count(*) of both tbl_A and tbl_b when i group the data by Library_Card_Type,Student_Class and Book_Type my tried queries were:

sel count(*) AS Count_of_tbl_A,Library_Card_Type AS Library_Card_Type ,Book_Type AS Book_Type,Student_Class AS Student_Class group by 2,3,4 order by 2,3,4 from tbl_A ;
Union all
sel count(*) AS Count_of_tbl_B,Library_Card_Type AS Library_Card_Type ,Book_Type AS Book_Type,Student_Class AS Student_Class group by 2,3,4 order by 2,3,4 from tbl_B;
sel (Count_of_tbl_A - Count_of_tbl_B) As Overall_Difference;

other approach was

 Sel Count_of_tbl_B from (sel count(*) AS Count_of_tbl_B,Library_Card_Type AS Library_Card_Type ,Book_Type AS Book_Type,Student_Class AS Student_Class group by 2,3,4 order by 2,3,4 from tbl_B) A,
    Sel Count_of_tbl_A from (sel count(*) AS Count_of_tbl_A,Library_Card_Type AS Library_Card_Type ,Book_Type AS Book_Type,Student_Class AS Student_Class group by 2,3,4 order by 2,3,4 from tbl_A) B,
    (Count_of_tbl_A - Count_of_tbl_B) as minus_result sample 10;
and the inner join also
select
 (sel count(*) AS Count_of_tbl_A,Library_Card_Type AS Library_Card_Type ,Book_Type AS Book_Type,Student_Class AS Student_Class group by 2,3,4 order by 2,3,4 from tbl_A)  AS Count_of_tbl_A
, (sel count(*) AS Count_of_tbl_A,Library_Card_Type AS Library_Card_Type ,Book_Type AS Book_Type,Student_Class AS Student_Class group by 2,3,4 order by 2,3,4 from tbl_B)  AS Count_of_tbl_B
,A.Library_Card_Type AS Library_Card_Type1
,B.Library_Card_Type AS Library_Card_Type
,A.Book_Type AS Book_Type1
,B.Book_Type AS Book_Type
,A.Student_Class AS Student_Class1
,B.Student_Class AS Student_Class
,(Count_of_tbl_A - Count_of_tbl_B ) AS Difference_of_Count 
FROM  tbl_B B 
INNER JOIN 
tbl_A A   
ON  A.Library_Card_Type=B.Library_Card_Type AND
    A.Book_Type=B.Book_Type  AND
    A.Student_Class=B.Student_Class;

I don't know how to do it please guide me.

Upvotes: 0

Views: 216

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You need to do the counts separately in two Derived Tables and then join them. But as there might be a combination which only exists on one of those table you must switch to a Full Outer Join plus COALESCE:

select
   coalesce(A.Library_Card_Type, B.Library_Card_Type) as Library_Card_Type
  ,coalesce(A.Book_Type, B.Book_Type) as Book_Type
  ,coalesce(A.Student_Class, B.Student_Class) as Student_Class
  ,coalesce(A.cnt,0) -  coalesce(B.cnt, 0) as difference
  ,coalesce(A.cnt,0) as tblA_cnt
  ,coalesce(B.cnt,0) as tblB_cnt
from
 (
   select
      Library_Card_Type
     ,Book_Type
     ,Student_Class
     ,count(*) AS cnt
   from tbl_A
   group by 1,2,3
 ) as A
full join
 (
   select
      Library_Card_Type
     ,Book_Type
     ,Student_Class
     ,-count(*) AS cnt
   from tbl_B
   group by 1,2,3
 ) as B
  ON A.Library_Card_Type=B.Library_Card_Type
 AND A.Book_Type=B.Book_Type  
 AND A.Student_Class=B.Student_Class

Or you switch to a different approach, UNION both results and aggregate:

select
   Library_Card_Type
  ,Book_Type
  ,Student_Class
  ,sum(cnt) as difference
  ,max(cnt) as tblA_count
  ,abs(min(cnt)) as tblB_count
from
 (
   select
      Library_Card_Type
     ,Book_Type
     ,Student_Class
     ,count(*) AS cnt -- positive numbers
   from tbl_A
   group by 1,2,3

   union ALL -- more efficient than UNION

   select
      Library_Card_Type
     ,Book_Type
     ,Student_Class
     ,- count(*) AS cnt -- negative numbers
   from tbl_B
   group by 1,2,3
 ) as dt
group by 1,2,3

In both cases you might add a filter to show only differences, either

where difference <> 0  -- join query

or

having difference <> 0 -- union query

Upvotes: 1

Related Questions