Grouping by calculated experience in PostgreSQL

August 9, 2023

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:

Calculate totay years of experience for every consultant_profile_id
Group that data to present information how many consultant profiles with the same total years of experience are present?

Is there any way to do that in PostgreSQL?

https://www.db-fiddle.com/f/iiwSYyitSeZFoupCs3Jcf/1

>Solution :

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.