sqlpostgresqlpostgresql-15

Grouping by calculated experience in PostgreSQL


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:

  1. Calculate totay years of experience for every consultant_profile_id
  2. 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.