CODEWITHSUNDEEP
sqloracle-database
Core_Dev
Core_Dev

Reputation: 33

Display count of subquery in outer query

I want to see the number of departments from Query2 as a new column in Query1. How can I do that?

Query 1:
SELECT
    location_id,
    street_address,
    postal_code,
    city,
    state_province,
    country_id
FROM
    locations;

Query 2:
SELECT
    location_id,
    COUNT(department_id)
FROM
    departments 
group by location_id;

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

One method is a correlated subquery:

select l.*,
       (select count(*) from departments d where d.location_id = l.location_id
       ) as num_departments
from locations l;

Upvotes: 2

Related Questions