1

I'm trying to extract some statistics from a Postgres database and I made this query:

SELECT
  city,
  job_count,
  TO_CHAR(job_count * 100 / SUM(job_count) OVER (), '90D00%') AS job_share
FROM (
  SELECT
    localities.name as city,
    COUNT(*) AS job_count
  FROM jobads
  JOIN localities ON jobads.locality_uuid = localities.uuid
  WHERE jobads.external = true
  GROUP BY localities.name
  ORDER BY job_count DESC
) AS job_count_table;

Here's the result it returns:

         city          | job_count | job_share
-----------------------+-----------+-----------
 City #1               |      1300 |  13.00%
 City #2               |       700 |   7.00%
 City #3               |       400 |   4.00%
 ...
 City #1200            |         1 |   0.01%
(1200 rows)

It returns hundred of rows and most of them have a really low job count. I would like to merge all row that have a job_count less then 100 into a single row that would so the output would look something like this:

         city          | job_count | job_share
-----------------------+-----------+-----------
 City #1               |      1300 |  13.00%
 City #2               |       700 |   7.00%
 City #3               |       400 |   4.00%
 Other cities          |      2000 |  20.00%
(4 rows)

Any idea how can I do that?

1 Answer 1

0

You can use another level of aggregation with a conditional value to group by.

SELECT
  CASE WHEN jc.job_count >= 100 THEN jc.city ELSE 'Other cities' END AS city,
  SUM(jc.job_count) AS job_count,
  TO_CHAR(SUM(jc.job_count) * 100 / SUM(SUM(jc.job_count)) OVER (), '90D00%') AS job_share
FROM (
  SELECT
    l.name as city,
    COUNT(*) AS job_count
  FROM jobads ja
  JOIN localities l ON ja.locality_uuid = l.uuid
  WHERE ja.external
  GROUP BY l.name
) AS jc
GROUP BY
  CASE WHEN jc.job_count >= 100 THEN jc.city ELSE 'Other cities' END
ORDER BY
  SUM(jc.job_count) DESC;
1
  • Thanks a lot! Just a small typo, instead of jc.name there should be jc.city Commented May 8 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.