CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Mohammad Sufian Al-Omar
Mohammad Sufian Al-Omar

Reputation: 139

Select count(*) from multiple tables with null values

      Table 1            Table 2 
        Class | VAL      Class | VAL
        ------|-----     ------|-----
          A   |  1         A   |  1     
          A   |  1         A   |  1
          A   |  1         A   |  1
          B   |  1         B   |  1
          A   |  1        
          B   |  1  
          C   |  1

If I have two tables as above and I need the result as the following table Result needed:

Class | T1  | T2
------|-----|-----
  A   |  4  |  3
  B   |  2  |  1
  C   |  1  |  0

I'm trying this query but it's not working.

SELECT [Class],COUNT(VAL) FROM dbo.T1 group by [Class]) 
UNION
SELECT [Class],COUNT(VAL) FROM dbo.T2 group by [Class])

Upvotes: 1

Views: 886

Answers (4)

Sabarish
Sabarish

Reputation: 74

   SELECT t1.class,
          COUNT(t1.class) AS T1,
          X.T2Count AS T2
    FROM @table1 t1
    OUTER APPLY(
          SELECT COUNT(val) AS T2Count
          FROM @table2 t2
          WHERE t2.class = t1.class
    )X
    GROUP BY t1.class,X.T2Count
    ORDER BY t1.class

Upvotes: 1

DVT
DVT

Reputation: 3127

SELECT COALESCE(a.Class, b.Class) AS [Class], COALESCE(a.T1,0) AS T1, COALESCE(b.T2,0) AS T2
FROM
(SELECT [Class],COUNT(VAL) AS T1 FROM dbo.T1 GROUP BY [Class]) a
FULL OUTER JOIN
(SELECT [Class],COUNT(VAL) AS T2 FROM dbo.T2 GROUP BY [Class]) b
ON a.Class = b.Class

Another possible solution

SELECT
    a.[Class]
    , COUNT(a.VAL1) AS T1
    , COUNT(a.VAL2) AS T2
FROM (
    SELECT [Class], VAL AS VAL1, NULL AS VAL2 FROM dbo.T1
    UNION ALL
    SELECT [Class], NULL AS VAL1, VAL AS VAL2 FROM dbo.T2
    ) a
GROUP BY
    a.Class;

Upvotes: 3

Ric_R
Ric_R

Reputation: 155

Select Class, sum(T1) as T1, sum(T2) AS T2
From
(
 Select Class, VAL AS T1, 0 as T2
 From Table1
Union All
 Select Class, 0 as T1, VAL AS T2
 From Table2
 ) A
 group by A.Class

Upvotes: 1

GuidoG
GuidoG

Reputation: 12014

And another approach here

declare @table1 table (class varchar(1) null, val int)
declare @table2 table (class varchar(1) null, val int)

insert into @table1 values ('A', 1)
insert into @table1 values ('A', 1)
insert into @table1 values ('A', 1)
insert into @table1 values ('B', 1)
insert into @table1 values ('A', 1)
insert into @table1 values ('B', 1)
insert into @table1 values ('C', 1)

insert into @table2 values ('A', 1)
insert into @table2 values ('A', 1)
insert into @table2 values ('A', 1)
insert into @table2 values ('B', 1)

select t.Class, 
       (select count(val) from @table1 where class = t.class),
       (select count(val) from @table2 where class = t.class)
from  ( select class 
        from   @table1 t1
        union 
        select class
        from   @table2 t1
      ) t

this returns 

Class   T1  T2  
A       4   3   
B       2   1   
C       1   0

Upvotes: 1

Related Questions