CODEWITHSUNDEEP
sqlpostgresql
David Lojudice Sb.
David Lojudice Sb.

Reputation: 1302

Calculate percentage / aggregation based on a baseline row

I would like to calculate the productivity of a sales team compared to a specific team member.

Given this query:

with t1 (rep_id, place_id, sales_qty) as (values
    (0,  1,  3),
    (1,  1,  1),
    (1,  2,  2),
    (1,  3,  4),
    (1,  4,  1),
    (2,  2,  1),
    (2,  3,  3)
)
select
    rep_id,
    count(distinct place_id) as qty_places,
    sum(sales_qty) as qty,
    sum(sales_qty) / count(place_id) as productivity
from 
    t1
group by
    rep_id

result:

rep_id | qty_places | qty_sales | productivity
---------------------------------------------
0      | 1          | 6         | 6
1      | 4          | 22        | 5
2      | 2          | 9         | 4

I would like to have the productivity of the team based on the productivity of rep_id = 1, so I would like to have something like this:

rep_id | qty_places | qty_sales | productivity | productivity %
--------------------------------------------------------------
0      | 1          | 6         | 6            | 1.2
1      | 4          | 22        | 5            | 1           <- Baseline
2      | 2          | 9         | 4            | 0.8

How can I achieve that with SQL on PostgreSQL?

Upvotes: 0

Views: 75

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Simply use conditional aggregation. I would do this using a subquery:

select t.*,
       productivity / max(productivity) filter (where rep_id = 1) over ()
from (select rep_id,
             count(distinct place_id) as qty_places,
             sum(sales_qty) as qty,
             sum(sales_qty)::numeric / count(place_id) as productivity
      from t1
      group by rep_id
     ) t

Here is a db<>fiddle.

Note that you can actually express this without the subquery, but I think that just makes the query more complicated.

Upvotes: 0

SABER
SABER

Reputation: 373

this should do the trick 

with t1 (rep_id, place_id, sales_qty) as (values
(0,  1,  3),
(1,  1,  1),
(1,  2,  2),
(1,  3,  4),
(1,  4,  1),
(2,  2,  1),
(2,  3,  3)
),
cte as (select
rep_id,
count(distinct place_id) as qty_places,
sum(sales_qty) as qty,
sum(sales_qty) / count(place_id) as productivity
from 
    t1
group by
    rep_id)

select rep_id, qty_places, qty, productivity,
    productivity::numeric/(select productivity::numeric from cte where rep_id = 1) 
    as  productivity_percent from cte

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

We can try computing the rep_id = 1 figures in a separate CTE, and then cross join that to your current table:

WITH cte AS (
    SELECT SUM(CASE WHEN rep_id = 1 THEN sales_qty ELSE 0 END) /
        COUNT(CASE WHEN rep_id = 1 THEN 1 END) AS baseline
    FROM t1
)

SELECT
    rep_id,
    COUNT(DISTINCT place_id) AS qty_places,
    SUM(sales_qty) AS qty,
    SUM(sales_qty) / COUNT(place_id) AS productivity,
    (1.0*SUM(sales_qty) / COUNT(place_id)) / t2.baseline AS productivity_pct
FROM t1
CROSS JOIN cte t2
GROUP BY
    t1.rep_id, t2.baseline;

Demo

Upvotes: 0

Related Questions