CODEWITHSUNDEEP
sql-serverc#-4.0
Hamonbatra
Hamonbatra

Reputation: 178

sql select count from table by another table values

i am beginner to sql database i want some help .... i have two tables (families_table ) and (children_table ) that related via family_id so every family in families table has many of children in children table ... i want to select some families from family table , and know the number of children for all selected families .. i tries to do this by 

select count (*) from dbo.Children where family_id in (select top 50 percent * from dbo.Families where economic_state = 'a' or economic_state = 'j')

Upvotes: 0

Views: 1830

Answers (4)

sudhansu63
sudhansu63

Reputation: 6190

You can use Common Table Expression(Cte) to write such query with readability.

;With CteFamily AS
(
SELECT family_id FROM dbo.Families 
--WHERE --Put your conditions to filter family
),
--get childrens count, with family id for selected family
CteChildrenCount AS
(
 SELECT family_id , Count(*) As ChildrenCount
 FROM  dbo.Children 
 WHERE family_id  IN (SELECT family_id FROM CteFamily)
 GROUP BY family_id 
),
--final query to get all other details from family table
CteFamilyDetails AS
(
  SELECT f.economic_state,f.family_id ,ChildrenCount   --Add extra columns from family       --table here
  FROM dbo.Families f
 INNER JOIN CteChildrenCount  c
 ON f.family_id = c.family_id
)
SELECT * FROM CteFamilyDetails;    --End of Cte end with semicolon.

Upvotes: 0

Szymon
Szymon

Reputation: 43023

You can use group by and count for such a query:

select f.family_id, count(*)
from dbo.Families f
inner join dbo.Children c ON c.family_id = f.family_id
where f.economic_state = 'a' or f.economic_state = 'j'
group by f.family_id

EDIT:

If you need to return only top 50 percent, you can simply add it to the query above. As it does the join and count first, it will return 50 percent from the join result:

select top 50 percent f.family_id, count(*)
from dbo.Families f
inner join dbo.Children c ON c.family_id = f.family_id
where f.economic_state = 'a' or f.economic_state = 'j'
group by f.family_id

Upvotes: 1

user2341459
user2341459

Reputation: 81

use join and group by:

SELECT children.fid, families.eco_state, count(children.fid) FROM children, families where children.fid= families.id and families.eco_state = 'a'

Group by children.fid

Upvotes: 0

David Garrison
David Garrison

Reputation: 2880

Modified from Szymon's answer to allow you to include other columns from the table.

select *
FROM
    (select f.family_id, count(*) children
    from dbo.Families f
    inner join dbo.Children c ON c.family_id = f.family_id
    where f.economic_state = 'a' or f.economic_state = 'j'
    group by f.family_id) fc
JOIN dbo.Families f ON f.family_id = fc.family_Id

Upvotes: 1

Related Questions