Richard Richard - 7 months ago 13
SQL Question

Aggregate query across two tables in SQL?

I'm working in BigQuery. I've got two tables:

TABLE: orgs
code: STRING
group: STRING

TABLE: org_employees
code: STRING
employee_count: INTEGER


The
code
in each table is effectively a foreign key. I want to get all unique
group
s, with a count of the orgs in them, and (this is the tricky bit) a count of how many of of those orgs only have a single employee. Data that looks like this:

group,orgs,single_handed_orgs
00Q,23,12
00K,15,7


I know how to do the first bit, get the unique
group
s and count of associated orgs from the
orgs
table:

SELECT
count(code), group
FROM
[orgs]
GROUP BY group


And, I know how to get the count of single-handed orgs from the practice table:

SELECT
code,
(employee_count==1) AS is_single_handed
FROM
[org_employees]


But I'm not sure how to glue them together. Can anyone help?

Answer

for BigQuery: legacy SQL

SELECT
  [group], 
  COUNT(o.code) as orgs, 
  SUM(employee_count = 1) as single_handed_orgs
FROM [orgs] AS o
LEFT JOIN [org_employees] AS e
ON e.code  = o.code
GROUP BY [group]

using LEFT JOIN in case if some codes are missing in org_employees tables

for BigQuery: standard SQL

SELECT
  grp, 
  COUNT(o.code) AS orgs , 
  SUM(CASE employee_count WHEN 1 THEN 1 ELSE 0 END) AS single_handed_orgs
FROM orgs AS o
LEFT JOIN org_employees AS e
ON e.code  = o.code
GROUP BY grp  

Note use of grp vs group - looks like standard sql does like use of Reserved Keywords even if i put backticks around

Comments