I am learning the Psql function by myself and I want to return only one row and one column name: dep_people and don't know how to solve this question in one row and one column name. Please help me to understand Psql function.
Preview table
SELECT * FROM test_sch.apollo_org_job_function;
|-------------------------------------------|
|organization_id |department |no_people|
|-------------------------------------------|
| 2a |accounting | 3 |
| 1a |engineering | 2 |
| 1a |entrepreneurship| 1 |
| 1a |human resources | 4 |
|-------------------------------------------|
I am at here
SELECT
department, COUNT(*) as "no_people"
FROM test_sch.apollo_org_job_function
GROUP BY department
ORDER BY department;
My return is:
|--------------------------|
|department |no_people|
|--------------------------|
|accounting | 3 |
|engineering | 2 |
|entrepreneurship| 1 |
|human resources | 4 |
|--------------------------|
I want this output
Calling the function:-
select * from test_sch.return_dep_people('1a')
as f(dep_people text);
Expected output:-
------------------------------------------------------|
|dep_people |
------------------------------------------------------|
|Engineering:2 , human_resources:4, entrepreneurship:1|
-------------------------------------------------------
NOTE: the result should contains only 1 column named “dep_people” and only 1 row which is in String, (“Engineering:2 , human_resources:4, entrepreneurship:1” is a single String)
CodePudding user response:
You can achieve this using JSON_BUILD_OBJECT
and JSON_AGG
:
WITH data AS (
SELECT department, COUNT(*) as "no_people"
FROM apollo_org_job_function GROUP BY department ORDER BY department
) SELECT
JSON_AGG(JSON_BUILD_OBJECT(department, no_people)) as "dep_people"
FROM data;
PostgreSQL JSON_AGG JSON_BUILD_OBJECT
CodePudding user response:
I guess, STRING_AGG may help you. Something like this:
SELECT
STRING_AGG(d.department || ': ' || CAST(d.no_people AS text), ', ') AS dep_people
FROM ([your initial query]) AS d