Zulqarnain Jalil Zulqarnain Jalil - 6 months ago 23
SQL Question

Calculate Count of data with join and 'where' claus

I have Table With fields ([Dist_Id],[Name],[FatherContactNumber])
Now I need the count of all childerns in a district and the count of childerns with FatherContactNumber !=''

I have created the query to find all childerns where FatherContactNumber !=''

But i Also need Numbers of Total Childerns

select d.Dist_Name, count(*) [Total_Childernd] from Children c
inner join District d on c.Dist_Id=d.Dist_Id
where c.FatherContactNumber !=''
group by d.Dist_Name

Dist_Name Total_Childernd_With_Numbers
-------------------------------------------
Dist_Name1 313
Dist_Name2 215
Dist_Name3 1624
Dist_Name4 38
Dist_Name5 2874
Dist_Name6 118
Dist_Name7 72
Dist_Name8 3590


My Desired Output is

Dist_Name Total_Childernd_With_Parent_Numbers Total_Childernd
----------------------------------------------------------------------------
Dist_Name1 | 313 |
Dist_Name2 | 215 |
Dist_Name3 | 1624 |
Dist_Name4 | 38 |
Dist_Name5 | 2874 |
Dist_Name6 | 118 |
Dist_Name7 | 72 |
Dist_Name8 | 3590 |


I don't need to to create a nested qury.
Please suggest the best way to do this because i have Billions of records.

SHD SHD
Answer Source

How this query,

select d.Dist_Name
        ,count(*) [Total_Childernd] 
        ,count(case when c.FatherContactNumber !='' then 1 else null end) as [Total_Childernd_With_Numbers]
from Children c
inner join District d on c.Dist_Id=d.Dist_Id
group by d.Dist_Name