CREATE A QUERY WITH GROUP BY

Question

I am trying to count how many "subsidiary companies" work for each "parent company", I came up with something like this...

 select cs.contract_number, jd.Job_description, c.Parent_Company,
        count(sub.subsidiary_company)
 from contracts cs
     join Job jd
         on jd.ID = cs.ID
     join Company c
         on cs.Company_ID = c.Company_ID
     join Sub_Company sub
         on c.Company_ID = sub.Prime
 group by cs.contract_number, jd.Job_description, c.Parent_Company;

This is an example of the result I am looking for...

however, I am not getting the results that I need. I am trying to create a query that displays the distinct count of "subsidiary companies" that work under each "parent company" for each "Job description". Can you please assist? Thanks.

Help us help you - Please share your table structure, some sample data, the result you get for that sample and the result you're trying to get for it. — Mureinik, Jun 01 '17 at 17:31

score 1 · Answer 1 · answered Jun 01 '17 at 19:57

For better help you need to provide table structures and sample data in an easily consumable format like this:

-- Table structure and sample data 
DECLARE @contracts TABLE (Id int identity, contract_number varchar(100), Company_Id int);
DECLARE @job TABLE (Id int identity, Job_description varchar(100));
DECLARE @Company TABLE (Company_Id int identity, parent_company varchar(100));
DECLARE @Sub_Company TABLE (Company_Id int, Parent_Company_Id int);

INSERT @contracts(contract_number, Company_Id) 
  VALUES ('001122',1), ('009922',1), ('123ABC',2), ('XXXYYYZZZ',2);
INSERT @job(Job_description) 
  VALUES ('Fun stuff'), ('Hard Stuff'), ('Dumb stuff');
INSERT @Company(parent_company) 
  VALUES ('A Co'), ('B Co');
INSERT @Sub_Company(Parent_Company_Id, Company_Id) 
  VALUES (1,10), (1,11), (1, 20), (1, 30), (2, 400), (2, 500);

-- Select statements to review the data
SELECT * FROM @contracts;
SELECT * FROM @Company;
SELECT * FROM @job;
SELECT * FROM @Sub_Company;

You can just copy/paste the above code and run it locally.

I am trying to create a query that displays the distinct count of "subsidiary companies" that work under each "parent company" for each "Job description".

Your GROUP BY statement will look like this:

group by jd.Job_description, c.Parent_Company, cs.contract_number

Your solution will look something like this

-- Table structure and sample data 
DECLARE @contracts TABLE (Id int identity, contract_number varchar(100), Company_Id int);
DECLARE @job TABLE (Id int identity, Job_description varchar(100));
DECLARE @Company TABLE (Company_Id int identity, parent_company varchar(100));
DECLARE @Sub_Company TABLE (Company_Id int, Parent_Company_Id int);

INSERT @contracts(contract_number, Company_Id) 
  VALUES ('001122',1), ('009922',1), ('123ABC',2), ('XXXYYYZZZ',2);
INSERT @job(Job_description) 
  VALUES ('Fun stuff'), ('Hard Stuff'), ('Dumb stuff');
INSERT @Company(parent_company) 
  VALUES ('A Co'), ('B Co');
INSERT @Sub_Company(Parent_Company_Id, Company_Id) 
  VALUES (1,10), (1,11), (1, 20), (1, 30), (2, 400), (2, 500);

-- solution
select jd.Job_description, c.Parent_Company, cs.contract_number, total = count(DISTINCT Sub.Company_Id)
from @contracts cs
join @job jd on jd.ID = cs.ID
join @Company c on cs.Company_ID = c.Company_ID
join @Sub_Company sub on c.Company_ID = sub.Parent_Company_Id
group by jd.Job_description, c.Parent_Company, cs.contract_number ;

Update the sample data I provided to include the correct columns and update the sample data to look more accurate. Next post a screen shot or something that shows what results you want. Do that and you'll get good help fast.

CREATE A QUERY WITH GROUP BY

1 Answers1