CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005sql-server-2008-r2
user2514925
user2514925

Reputation: 949

Get Count values from 2 different table

I'm Working with Sql Server 2008.There are 3 tables table1,table2,table3

table 1, 

Id  Name  group  
1   ddd   a       
2   aaa   b       
3   sss   a

table2 Contains:

Id Name group
1  fff   c
2  gg    a
3  saa   b

table 3,

Id group
1  a
2  b
3  c

I want to get the Following Result,

group     count(table1)   count(table2)
a           2               1
b           1               1
c           0               1

What Query i can write to get Appropriate Result

Upvotes: 0

Views: 90

Answers (4)

Jesuraja
Jesuraja

Reputation: 3844

Try using CROSS APPLY:

SELECT group, T1.T1Count, T2.T2Count
FROM   Groups G
CROSS APPLY
  (SELECT COUNT(*) AS T1Count FROM table1 AS IT1 WHERE G.group = IT1.group) T1
CROSS APPLY
  (SELECT COUNT(*) AS T2Count FROM table2 AS IT2 WHERE G.group = IT2.group) T2

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18767

Try this:

SELECT T3.[group],
       COUNT(T1.[group]) as Count1,
       COUNT(T2.[group]) as Count2
FROM Table3 T3 LEFT JOIN
     Table1 T1 on T3.[group]=T1.[group] LEFT JOIN
     Table2 T2 on T3.[group]=T2.[group]
GROUP BY T3.[group]

Result:

GROUP   COUNT1  COUNT2
a       2       2
b       1       1
c       0       1

See result in SQL Fiddle.

Explanation:

COALESCE will return the first parameter which is not null. So if count is null, it will return 0.

Upvotes: 0

Ryno Coetzee
Ryno Coetzee

Reputation: 517

insert into @table1 
values (1, 'aaa', 'a'),
(1, 'bbb', 'b'),
(1, 'ccc', 'c'),
(1, 'ddd', 'd')


insert into @table2 (id, name, groupcode) 
values (1, 'aaa', 'a'),
(1, 'bbb', 'b'),
(1, 'ddd', 'd')

insert into @table3 (id, name, groupcode) 
values (1, 'aaa', 'a'),
(1, 'bbb', 'b'),
(1, 'ccc', 'c'),
(1, 'ddd', 'd')

SELECT tab1.groupcode,
       COUNT(tab1.groupcode) as Count1,
       COUNT(tab2.groupcode) as Count2,
       COUNT(tab3.groupcode) as Count3
FROM @table1 tab1 
INNER JOIN @Table2 tab2 on tab1.groupcode = tab2.groupcode 
INNER JOIN @Table3 tab3 on tab1.groupcode = tab3.groupcode

/*
    USE LEFT JOINS IF YOU WANT TO RETURN ALL 
    GROUPS EVEN IF THERE IS NOT A MATCH IN
    ONE OF THE TABLES
*/


GROUP BY 
tab1.groupcode

Upvotes: 0

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11775

You can try like this

SELECT
   group,
   (SELECT COUNT(*) FROM table1 WHERE group=G.group)  AS table1count,
   (SELECT COUNT(*) FROM table2 WHERE group=G.group)  AS table2count
FROM table3 G

Upvotes: 1

Related Questions