In my PostgreSQL database, I have the following table:
CREATE TABLE public.experiences (
id bigint NOT NULL,
consultant_profile_id bigint NOT NULL,
position character varying NOT NULL,
years INTEGER NOT NULL
);
--Consultant Profile #1 experiences:
INSERT INTO experiences (id, consultant_profile_id, position, years) VALUES (1, 1, 'CEO', 3);
INSERT INTO experiences (id, consultant_profile_id, position, years) VALUES (2, 1, 'CTO', 2);
--Consultant Profile #2 experiences:
INSERT INTO experiences (id, consultant_profile_id, position, years) VALUES (3, 2, 'Intern', 1);
INSERT INTO experiences (id, consultant_profile_id, position, years) VALUES (4, 2, 'Data Analyst', 1);
I need a query that will represent data in following way:
---------------------------------------------------------------------------------
total_years_of_experience_per_consultant | count_of_consultant_profiles
---------------------------------------------------------------------------------
5 | 1
2 | 1
So the query should do the following:
Is there any way to do that in PostgreSQL?
We can use a two tier aggregation approach:
WITH cte AS (
SELECT SUM(years) AS total_years
FROM experiences
GROUP BY consultant_profile_id
)
SELECT
total_years AS total_years_of_experience_per_consultant,
COUNT(*) AS count_of_consultant_profiles
FROM cte
GROUP BY total_years,
ORDER BY total_years DESC;
The CTE finds total years of experience per consultant, and the second query aggregates by those years to find the counts.