CODEWITHSUNDEEP
sqlsql-server-2012
mbayrak
mbayrak

Reputation: 31

SQL Count from Sub Table

I want to count some columns from sub table. My table structure is below:

Persons

+---+----------+--------+
| Pid | Name   |Surname |
+---+----------+--------+
| 1   | Per A  | D      |
| 2   | Per B  | E      |
| 3   | Per C  | F      
+----+---------+--------+

Childs

+---+---------+-------------------+------------+-----+
| Cid | CName          | School   | Sex        | Pid |
+---+---------+-------------------+------------+-----+
| 1   | John           | High     |    Man     | 1   |
| 2   | Alice          | Primary  |    Woman   | 2   |
| 3   | Mel            | High     |    Man     | 3   |
| 4   | Angelina       | High     |    Woman   | 2   |
+----+---------+------------------+------------+-----+

So I want to output

+---+----------+------+---------+--------+---+--------------+
| Pid| PerName | High | Primary | Woman  | Man | ChildCount | 
+---+----------+------+---------+--------+-----+------------+
| 1  | Per A   | 1    | 0       | 0      | 1   | 1          |
| 2  | Per B   | 1    | 1       | 2      | 0   | 2          |
| 3  | Per C   | 1    | 0       | 0      | 1   | 1          |
+----+---------+------+---------+--------+-----+------------+

How can I get this output?

I try this method but i have more columns like this to calculate belongs to Child table. So I get slow query results.

select Pid,Name,Surname,
  (select count(*) from Childs where Persons.Pid=Childs.Pid) ChildCount, 
  (select count(*) from Childs where Persons.Pid=Childs.Pid and School='Primary') Primary 
from Persons

Upvotes: 1

Views: 127

Answers (2)

Nidhi257
Nidhi257

Reputation: 967

Try This One:

select Pid,Name,Surname,
ifNull((select count(*) from Childs where Persons.Pid=Childs.Pid),0) ChildCount,
ifNull((select count(*) from Childs where Persons.Pid=Childs.Pid AND School='High' GROUP By Childs.Pid),0) High,
ifNull((select count(*) from Childs where Persons.Pid=Childs.Pid AND School='Primary' GROUP By Childs.Pid),0) 'primary',
ifNull((select count(*) from Childs where Persons.Pid=Childs.Pid AND Sex='Woman' GROUP By Childs.Pid),0) Woman,
ifNull((select count(*) from Childs where Persons.Pid=Childs.Pid AND Sex='Man' GROUP By Childs.Pid),0) Man

from Persons;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can do this with join and conditional aggregation:

select p.Pid, p.Name,
       sum(case when c.school = 'High' then 1 else 0 end) as high,
       sum(case when c.school = 'Primary' then 1 else 0 end) as primary,
       sum(case when c.sex = 'Man' then 1 else 0 end) as Man,
       sum(case when c.sex = 'Woman' then 1 else 0 end) as Woman,
       count(*) as ChildCount
from persons p left join
     childs c
     on p.pid = c.pid
group by p.Pid, p.Name;

Upvotes: 2

Related Questions