CODEWITHSUNDEEP
sql-server
khalid shingaly
khalid shingaly

Reputation: 23

How to make query to count values from two tables

I have three tables main 'maintable' table and two sub tables 'table1' and 'table2' the main table 'maintable' contains tow columns 'ID' and 'name' like that:

 ID    name
....  ......
 1    Khalid
 2     Jone
 3    Steve

and the first sub table 'table1' contains 't1ID' and 'column' and 'ID' (foreign key) from 'maintable' like that:

  t1ID    column     ID
 ......  .......   ....
   1      Value      1
   2      Value      1
   3      Value      1
   4      Value      2

and the second sub table 'table2' contains 't2ID' and 'column' and 'ID' (foreign key) from 'maintable' like that:

  t2ID    column     ID
 ......  .......   ....
   1      Value     2
   2      Value     1
   3      Value     1
   4      Value     3

I want to make query to find count of (table1.ID) as A and count of (table2.ID) as B like that:

 name     A    B
......   ...  ...
khalid    3    2
Jone      1    1
Steve     0    1

Upvotes: 2

Views: 55

Answers (3)

Sarvjeet Verma
Sarvjeet Verma

Reputation: 106

Try this :

select name, 
 (select count(t1.ID) from table1 t1 where t1.ID = main.ID) as A,
 (select count(t2.ID) from table2 t2 where t2.ID = main.ID) as B
from maintable main

Upvotes: 2

zedfoxus
zedfoxus

Reputation: 37099

Try this:

with t0_t1 as (
  select 
    t.id,
    t.nm, 
    count(t1.id) as A
  from table0 t
  left join table1 t1 on t.id = t1.id
  group by t.id, t.nm
)
select t.nm, t.A, count(t2.id) as B
from t0_t1 t
left join table2 t2 on t.id = t2.id
group by t.nm, t.A

Example: http://sqlfiddle.com/#!6/341ff/10

create table table0 (id int, nm varchar(20));
insert into table0 values (1,'Khalid'),(2,'Jone'),(3,'Steve');

create table table1 (t1id int, col varchar(20), id int);
insert into table1 values 
(1, 'v', 1), (2, 'v', 1), (3, 'v', 1), (4, 'v', 2);

create table table2 (t2id int, col varchar(20), id int);
insert into table2 values
(1, 'v', 2), (2, 'v', 1), (3, 'v', 1), (4, 'v', 3);

Result:

|     nm | A | B |
|--------|---|---|
|  Steve | 0 | 1 |
|   Jone | 1 | 1 |
| Khalid | 3 | 2 |

Upvotes: 2

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23088

Try this out:

;with cte1 as (
    SELECT ID, COUNT(1) AS Cnt
    FROM table1
    GROUP BY ID
), cte2 as (
    SELECT ID, COUNT(1) AS Cnt
    FROM table2
    GROUP BY ID
)
SELECT m.name, ISNULL(cte1.Cnt, 0) AS A, ISNULL(cte2.Cnt, 0) AS B
FROM maintable m
    LEFT JOIN cte1 ON cte1.ID = m.ID
    LEFT JOIN cte2 ON cte2.ID = m.ID

It can also be done with subqueries, but I like CTEs more (query is more readable).

Upvotes: 2

Related Questions