human
Schema:
buildings (id, name, City, Height, Stories, Status)
Companies (id, name, Headquarters, Industry, Sales_billion, Profits_billion, Assets_billion, Market_Value_billion)
Office_locations (building_id, company_id, move_in_year)
Please answer with SQL.
assistant
Sure, I understand.
human
Count the number of building IDs
assistant
SELECT COUNT( id ) FROM buildings;
human
add a column for company id
assistant
SELECT COUNT( * ) , T1.company_id , T1.building_id FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id = T2.id JOIN Companies AS T3 ON T1.company_id = T3.id GROUP BY T1.building_id;
human
list the number of unique building ids for each company id
assistant
SELECT T1.company_id , COUNT( distinct T1.building_id ) FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id = T2.id JOIN Companies AS T3 ON T1.company_id = T3.id GROUP BY T1.company_id;
human
add a column for company name
assistant
SELECT T1.company_id , t3.name , COUNT( distinct T1.building_id ) FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id = T2.id JOIN Companies AS T3 ON T1.company_id = T3.id GROUP BY T1.company_id ;