Click here to Skip to main content
15,860,972 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,
I have two tables (Family, Family Members). I want a query to get count of Families and Count of Total Family Members Group by city. I have the following query but its only giving total family members.

TotalFamilies, TotalIndiviuals,City
6,6,abc,
268,268,Peshawar

SQL
select COUNT(Fam.FamilyID) as TotalFamilies, COUNT(*) as TotalIndiviuals, Fam.City from Family as Fam
inner join FamilyMembers FMem on
FMem.FamilyID=Fam.FamilyID
group by Fam.City


I shall be thankful for your help.

Usama
Posted

You will need to use subquery in the join

SQL
select COUNT(Fam.FamilyID) as TotalFamilies, Sum(CountFamilyMembers.totalFMem) as TotalIndiviuals, Fam.City From Family as Fam
inner join (Select FamilyID,Count(FamilyMemberId) as totalFMem from FamilyMembers Group By FamilyId) as CountFamilyMembers On fam.FamilyId=CountFamilyMembers.FamilyId 
group by Fam.City
 
Share this answer
 
Comments
Sarin VT 24-Jul-13 2:35am    
good one
You need to distinct members ;)
SQL
DECLARE @Family TABLE (FamilyID INT IDENTITY(1,1), FamilyName VARCHAR(50), City VARCHAR(50))

INSERT INTO @Family (FamilyName, City)
SELECT 'FamilyW1', 'Warsaw'
UNION ALL SELECT 'FamilyW2', 'Warsaw'
UNION ALL SELECT 'FamilyW3', 'Warsaw'
UNION ALL SELECT 'FamilyK1', 'Katowice'
UNION ALL SELECT 'FamilyK2', 'Katowice'
UNION ALL SELECT 'FamilyK3', 'Katowice'
UNION ALL SELECT 'FamilyK4', 'Katowice'
UNION ALL SELECT 'FamilyB1', 'Bialystok'
UNION ALL SELECT 'FamilyB2', 'Bialystok'
UNION ALL SELECT 'FamilyB3', 'Bialystok'

DECLARE @FamilyMembers TABLE (MemberID INT IDENTITY(1,1), FamilyID INT)

INSERT INTO @FamilyMembers (FamilyID)
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 4
UNION ALL SELECT 4
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
UNION ALL SELECT 6
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 8
UNION ALL SELECT 8
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 9
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 10
UNION ALL SELECT 10
UNION ALL SELECT 10
UNION ALL SELECT 10

SELECT Fam.City, COUNT(DISTINCT Fam.FamilyID) AS CountOfFamilies, COUNT(DISTINCT Mem.MemberID) AS NumberOfMembers
FROM @Family AS Fam INNER JOIN @FamilyMembers AS Mem ON Fam.FamilyID = Mem.FamilyID
GROUP BY Fam.City


Result:
Bialystok	3	12
Katowice	4	12
Warsaw	3	20
 
Share this answer
 
v3
Comments
Varsha Ramnani 24-Jul-13 2:20am    
It wont work because of the join with FamilyMembers table Count(Fam.familyId) returns Total number of Family Members and not Total number of families.
So, Applying Distinct on Count(Distinct Fam.FamilyID) instead of Count(Fmem.MemberId) will work perfectly but will be a bit slower.
Maciej Los 24-Jul-13 2:45am    
Thank you for your comment. See updated answer ;)
Try this script


SQL
declare  @Family table(FamilyID varchar(10) , City varchar(100))
declare  @FamilyMembers table(FamilyID varchar(10), MemberName varchar(100))

insert into @Family
select 'F1', 'C1' union select 'F2', 'C1' union select 'F3', 'C2' union select 'F4', 'C3'
insert into @FamilyMembers
select 'F1', 'M1' union select 'F1', 'M2' union select 'F1', 'M3' union select 'F3', 'M4' union select 'F3', 'M5'

SELECT
    COUNT(distinct(Fam.FamilyID)) as TotalFamilies,
     COUNT(FMem.FamilyID) as TotalIndiviuals, Fam.City
FROM @Family AS Fam
    INNER JOIN @FamilyMembers FMem
        ON FMem.FamilyID=Fam.FamilyID
 GROUP BY Fam.City
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900