Count records with sub query-CodePudding

I have two tables and I am trying to figure out how to make a sub query work to get a row count included. My tables are named in a way which will not make sense so I will present a simple example:

Table 1: country
columns:
country_id
country_name

Table 2: actors
columns:
country_id
actor_name

Ideally I would like to return:

results
country_name, actor_name, actor_count_country
USA, Denzil Washington, 3
USA, The Rock, 3
USA, Tom Hanks, 3
Australia, Nicole Kidman, 2
Australia, Russel Crow, 2

How would I write a query which lists all the actors, but also provides a count of the actors in each country like the above example?

CodePudding user response：

You can get it by simple aggregation function count.

You'd better add an index in country_name and actor_name.

SELECT
  c.country_name,
  a.actor_name,
  COUNT (c.country_id) AS actor_count_country
FROM
  country c
  INNER JOIN actors a
    ON a.country_id = c.country_id
GROUP BY c.country_name,
  a.actor_name

CodePudding user response：

If your version supports it we can use COUNT(~) OVER (PARTITION BY ~) to get the total number of actors.
Otherwise we can use a sub-query. I give an example of each method.

SELECT
  c.country_name,
  a.actor_name,
  COUNT(a.actor_name) OVER
    (PARTITION BY c.country_name)
    AS actor_count_country
FROM
  country c
  JOIN actors a
    ON a.country_id = c.country_id
GROUP BY c.country_name,
  a.actor_name

country_name | actor_name        | actor_count_country
:----------- | :---------------- | ------------------:
Australia    | Nicole Kidman     |                   2
Australia    | Russel Crow       |                   2
USA          | Denzil Washington |                   3
USA          | The Rock          |                   3
USA          | Tom Hanks         |                   3

SELECT
  c.country_name,
  a.actor_name,
  t.actor_count_country
FROM
  country c
JOIN actors a
 ON a.country_id = c.country_id
JOIN (
  SELECT 
    country_id,
    SUM(country_id) 
    AS actor_count_country
    FROM country
    GROUP BY country_id
  ) as t
 ON a.country_id = t.country_id
 ;

country_name | actor_name        | actor_count_country
:----------- | :---------------- | ------------------:
USA          | Denzil Washington |                   1
USA          | The Rock          |                   1
USA          | Tom Hanks         |                   1
Australia    | Nicole Kidman     |                   2
Australia    | Russel Crow       |                   2

db<>fiddle here