How to count array elements in subquery using ARRAY_AGG in Postgresql?

Question

The initial query finds the languages applied for each feature_id and makes the result into an array. I want to count the number of languages in each area, per feature_id, per version (new and old).

SELECT
  array_length(applied_languages_in_old_version, 1) AS COUNT1,
  array_length(applied_languages_in_new_version, 1) AS COUNT2
FROM (
SELECT
  t1.feature_id,
  t1.territory_type,
  t1.territory_category,
  (array_agg(DISTINCT t2.language), ', ') AS applied_languages_in_old_version,
  (array_agg(DISTINCT t4.language), ', ') AS applied_languages_in_new_verison
FROM kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory t1
LEFT OUTER JOIN kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory_name t2
ON t1.feature_id = t2.feature_id AND t2.name_type = 'PRIMARY_FOR_LANGUAGE'
JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory t3
ON t1.feature_id = t3.feature_id
LEFT OUTER JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory_name t4
ON t1.feature_id = t4.feature_id AND t4.name_type = 'PRIMARY_FOR_LANGUAGE') a
GROUP BY
  t1.feature_id,
  t1.territory_type,
  t1.territory_category
ORDER BY t1.feature_id;

RESULT: ERROR: column "t1.feature_id" must appear in the GROUP BY clause or be used in an aggregate function

score 0 · Answer 1 · answered Mar 23 '23 at 21:58

As the error message stated, all column in the select must be in the GROUP BY or use an aggregation function, i think you can use MAX but that could be a missunderstanding what you are looking for

SELECT
  MAX(array_length(applied_languages_in_old_version, 1)) AS COUNT1,
  MAX(array_length(applied_languages_in_new_version, 1)) AS COUNT2
FROM (
SELECT
  t1.feature_id,
  t1.territory_type,
  t1.territory_category,
  (array_agg(DISTINCT t2.language), ', ') AS applied_languages_in_old_version,
  (array_agg(DISTINCT t4.language), ', ') AS applied_languages_in_new_verison
FROM kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory t1
LEFT OUTER JOIN kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory_name t2
ON t1.feature_id = t2.feature_id AND t2.name_type = 'PRIMARY_FOR_LANGUAGE'
JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory t3
ON t1.feature_id = t3.feature_id
LEFT OUTER JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory_name t4
ON t1.feature_id = t4.feature_id AND t4.name_type = 'PRIMARY_FOR_LANGUAGE') a
GROUP BY
  t1.feature_id,
  t1.territory_type,
  t1.territory_category
ORDER BY t1.feature_id;

How to count array elements in subquery using ARRAY_AGG in Postgresql?

1 Answers1