CODEWITHSUNDEEP
sql
thiru
thiru

Reputation: 173

get the count of records from two tables in sql

I had three tables like ORG_DETAILS, DISTRICT_MASTER AND WORKER_DETAILS..

I want the count of number of organizations and related workers count based on district_name. here is the query i am trying......

 SELECT dm.DISTRICT_NAME ,
    count(od.Org_ID)as orgcount,
    count(wd.WORKER_ID)as workerscount
    from ORG_DETAILS od 
    left join WORKER_DETAILS wd  on wd.ORG_ID = od.ORG_ID
    left join DISTRICT_MASTER dm on od.DISTRICT_ID = dm.DISTRICT_ID

    GROUP BY dm.DISTRICT_NAME

i am getting duplicate values in count like, one extra org count and worker count....

please help me with this...

thank you.....

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The simplest way to fix your problem is to use count(distinct):

SELECT dm.DISTRICT_NAME ,
       count(distinct od.Org_ID)a s orgcount,
       count(distinct wd.WORKER_ID)as workerscount
from ORG_DETAILS od left join
     WORKER_DETAILS wd
     on wd.ORG_ID = od.ORG_ID left join
     DISTRICT_MASTER dm
     on od.DISTRICT_ID = dm.DISTRICT_ID
GROUP BY dm.DISTRICT_NAME;

For performance reasons, doing the aggregation along each dimension before the join performs better if the counts are high.

Upvotes: 2

Related Questions