CODEWITHSUNDEEP
sql
iml3g3nd
iml3g3nd

Reputation: 13

Sql count : taking count of rows joining 2 tables

Lets say i have 2 tables Master and slave . Master table contains master_name and master_id , Slave table has Slave_id, slave_name and master_id.

sample data.

master_id Master_name  Master_status      slave_id  slave_name  master_id status
1         x               online              1          a          1       online
2         y               online              2          b          1       online
3         z               offline             3          c          2       offline
                                              4          d          3       offline
                                              5          e          3       online

the expected result i m trying to obtain is,

master_id  no_of_slave
   1             2
   2             0

i want to get the no: of online slaves each online masters have.

sorry for the late edit.

Upvotes: 1

Views: 219

Answers (7)

Chhabiz
Chhabiz

Reputation: 1

  SELECT master_id, count(*) AS no_of_slave
    FROM slave
GROUP BY master_id
ORDER BY master_id;

Upvotes: 0

Bharadwaj
Bharadwaj

Reputation: 2553

You can use this. This will work.

select m.master_id,(select count(*) from slave s where s.master_id = m.master_id) as no_of_slave from master m

Upvotes: 0

Yohan Danvin
Yohan Danvin

Reputation: 910

You're not interested in master names so a join is not required, you have all necessary info in the Slave table:

select master_id, count(slave_id) as no_of_slave
from Slave
group by master_id
order by master_id --remove this line, if ordering of master ids is not important
;

If a given master id is not in this result, this means it has no slaves.

Upvotes: 0

Himanshu
Himanshu

Reputation: 32602

Use LEFT JOIN like this one:

SELECT m.master_id
     , count(s.slave_id) AS no_of_slave
FROM master m 
LEFT JOIN slave s 
     ON m.master_id = s.master_id
GROUP BY m.master_id;

Result:

╔═══════════╦═════════════╗
║ MASTER_ID ║ NO_OF_SLAVE ║
╠═══════════╬═════════════╣
║         1 ║           2 ║
║         2 ║           1 ║
║         3 ║           2 ║
╚═══════════╩═════════════╝

See this SQLFiddle

Upvotes: 5

Code Lღver
Code Lღver

Reputation: 15603

Use the below query:

Select m.master_id, count(s.master_id) as no_of_slave 
       FROM master m 
            JOIN slave s 
            ON m.master_id = s.master_id
            GROUP By m.master_id;

Upvotes: 1

Markus Mikkolainen
Markus Mikkolainen

Reputation: 3497

select 
    a.master_id,
    count(b.slave_id) 
from 
    master a,
    slave b 
where 
     a.master_id=b.master_id 
group by 
     a.master_id

Upvotes: 0

Ted
Ted

Reputation: 4067

select    master_id, count(1)
from      Slave
group by  master_id
order by  master_id

will just give you masters that have slaves, and you don't even need to join with master if tables are big

Upvotes: 0

Related Questions