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
Last activity: May 8, 2024, 02:11 PM