CODEWITHSUNDEEP
sqlsql-server
DataScience99
DataScience99

Reputation: 369

Counting number of records with a certain value and mapping to a different table

I have 3 tables

  1. parent_company
  2. company
  3. main

parent_company contains the following relevant columns:

parent_company_id    total_num    active_num

company contains the following relevant columns:

company_id    parent_company_id

NOTE: many company_ids can point to the same parent_company_id

main contains the following relevant columns:

company_id    current_status

total_num refers to the total number of company_ids (that point to the same parent_company_id) exist in the main table. active_num is the same, given the extra condition their status must be 'Active'.

I initially did this in python using pandas, but would prefer to now do this in SQL seeing I'm trying to cut out python entirely.

Given the following company and main tables

company:

company_id    parent_company_id
   468                1
   785                1
   781                1
   213                2
   896                3

main:

company_id    current_status
    781          Inactive
    468          Active 
    785          Active
    468          Inactive
    781          Active
    785          Active
    213          Inactive
    896          Active

I would want the parent_company table to look like this:

parent_company:

parent_company_id    total_num    active_num
        1                6            4
        2                1            0
        3                1            1

Thanks in advance for any help!

Upvotes: 0

Views: 29

Answers (2)

Valerica
Valerica

Reputation: 1630

Just join your 2 tables together on company_id: the first count will give you the total number and then just check the curent_status and create the total :)

declare @company as table (
    company_id int
    ,parent_company_id int
);

insert into @company values
(468, 1)
,(785, 1)
,(781, 1)
,(213, 2)
,(896, 3);


declare @main as table (
    company_id int
    ,curent_status varchar(1)
);

insert into @main values
    (781, 'I')
    ,(468, 'A')
    ,(785, 'A')
    ,(468, 'I')
    ,(781, 'A')
    ,(785, 'A')
    ,(213, 'I')
    ,(896, 'A')



select
    c.parent_company_id
    ,count(c.company_id) as total_num
    ,SUM(case when m.curent_status = 'A' then 1 else 0 end) as active_num
from @company c
inner join @main m
    on c.company_id = m.company_id
group by c.parent_company_id

Upvotes: 1

PeterHe
PeterHe

Reputation: 2766

This should do it:

UPDATE p
SET p.total_num=n.total_num,
    p.active_num=n.active_num
FROM parent_company p
INNER JOIN (
SELECT c.parent_company_id,
COUNT(*) AS total_num,
SUM(CASE WHEN m.status='Active' THEN 1 ELSE 0 END) AS active_num
FROM company c
INNER JOIN main m
ON c.company_id=m.company_id
GROUP BY c.parent_company_id) n
ON n.parent_company_id=p.parent_company_id;

Upvotes: 1

Related Questions