Sample Header Ad - 728x90

PostgreSQL - Sum all row that satisfy a condition into a single row

1 vote
1 answer
139 views
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?
Asked by Mateja Maric (13 rep)
May 8, 2024, 01:34 PM
Last activity: May 8, 2024, 02:11 PM