如何计算两个表中的值 [英] How to count values from two tables

问题描述

我有两张桌子（家庭，家庭成员）。我想要一个查询来按城市计算家庭数和总家庭成员数。我有以下查询，但它只给全家人。



TotalFamilies，TotalIndiviuals，City

6,6，abc，

268,268，Peshawar

 选择 COUNT（Fam.FamilyID） as  TotalFamilies，COUNT（*） as  TotalIndiviuals，Fam.City 来自 Family  as  Fam 
  inner   join  FamilyMembers FMem   
 FMem.FamilyID = Fam.FamilyID 
  group   by  Fam.City 
 

我将感谢您的帮助。



Usama

解决方案

您需要在加入中使用子查询

 选择 COUNT（Fam.FamilyID） as  TotalFamilies，Sum（CountFamilyMembers.totalFMem） as  TotalIndiviuals，Fam.City  From 家庭 as  Fam 
  inner   join （选择 FamilyID，Count（FamilyMemberId） as  totalFMem 来自 FamilyMembers 组 按 FamilyId） as  CountFamilyMembers  On  fam.FamilyId = CountFamilyMembers.FamilyId 
  group   by  Fam.City 

你需要不同的成员;）

  DECLARE   @ Family   TABLE （FamilyID  INT   IDENTITY （ 1  ， 1 ），FamilyName  VARCHAR （ 50  ），City  VARCHAR （ 50 ））
 
  INSERT   INTO   @ Family （FamilyName，City）
 < span class =code-keyword> SELECT  '  FamilyW1'，  Warsaw' 
  UNION   ALL   SELECT  '  FamilyW2'，'  Warsaw' 
  UNION   ALL   SELECT  '  FamilyW3'，'  Warsaw' 
  UNION   ALL   SELECT  < span class =code-string>'  FamilyK1'，'  Katowice' 
  UNION   ALL   SELECT  '  FamilyK2'，'  Katowice' 
  UNION  所有  SELECT  '  FamilyK3' ，'  Katowice' 
  UNION   ALL   SELECT  '  FamilyK4'，'  Katowice' 
  UNION   ALL   SELECT  '  FamilyB1'，'  Bialystok' 
  UNION   ALL   SELECT  '  FamilyB2'，'   Bialystok' 
  UNION   ALL   SELECT  '  FamilyB3'，< span class =code-string>'  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  < span class =code-keyword> SELECT   2  
  UNION   ALL   SELECT   3  
  UNION   ALL   SELECT   3  
  UNION   ALL   SELECT   3  
  UNION  所有  SELECT   3  
  UNION  < span class =code-keyword> 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  所有  SELECT   5  
  UNION   ALL   SELECT  < span class =code-digit> 6  
  UNION   ALL   SELECT   6  
  UNION   ALL   SELECT   6  
  UNION   ALL   SELECT   6  
  UNION   ALL   SELECT   7  
  UNION  所有 < span class =code-keyword> SELECT   7  
  UNION   ALL   SELECT   8  
  UNION   ALL   SELECT   8  
  UNION   ALL   SELECT   8  
  UNION  所有  SELECT   8  
  UNION  < span class =code-keyword> ALL   SELECT   9  
  UNION   ALL   SELECT   9  
  UNION   ALL   SELECT   9  
  UNION   ALL   SELECT   10  
  UNION  < span class =code-keyword> 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 

结果：

 Bialystok 3 12 
 Katowice 4 12 
华沙3 20 

试试这个剧本

 声明  @ Family   table （FamilyID  varchar （ 10 ），City  varchar （ 100 ））
 声明  @ FamilyMembers  表（FamilyID  varchar （ 10 ），MemberName  varchar （ 100 ））
 
  insert  进入  @ Family  
 选择 '  F1'，'  C1'  union  选择 '  F2'，'  C1'  union  选择 '  F3'，'  C2' < span class =code-keyword> union  选择 '  F4' ，'  C3' 
  insert  进入  @ FamilyMembers  
 选择 '  F1'，'  M1'  union  选择 '  F1'，'  M2'  union  选择 '  F1'，'  M3'  union  选择 '  F3'，< span class =code-string>'  M4'  union  选择 '  F3'，'  M5' 
 
  SELECT  
 COUNT（ distinct （Fam.FamilyID）） as  TotalFamilies，
 COUNT（FMem.FamilyID）  as  TotalIndiviuals，Fam.City 
  FROM   @家庭  AS  Fam 
  INNER  加入  @ FamilyMembers  FMem 
 开启 FMem.FamilyID = Fam.FamilyID 
  GROUP   BY  Fam.City 

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

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

解决方案

You will need to use subquery in the join

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

You need to distinct members ;)

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

Try this script

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

这篇关于如何计算两个表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！