CODEWITHSUNDEEP
sqlsum
Spurious
Spurious

Reputation: 2005

SQL query - percentage of sub sample

I got a SQL statement:

Select
ID, GroupID, Profit
From table

I now want to add a fourth column percentage of group profits. Therefore the query should sum all the profits for the same group id and then have that number divided by the profit for the unique ID.

Is there a way to do this? The regular sum function does not seem to do the trick.

Thanks

Upvotes: 1

Views: 285

Answers (4)

Tim Sanders
Tim Sanders

Reputation: 851

To provide an alternate answer, albeit less efficient, is to use a scalar subquery.

SELECT  ID, GroupId, Profit, (Profit/(SELECT sum(Profit) 
                                     FROM my_table 
                                     WHERE GroupId= mt.GroupId))*100 as pct
FROM my_table as mt

From the way it reads I'm not sure if you want "percentage of group profits" or you or want group_profit / individual profit

That's the way this sounds "Therefore the query should sum all the profits for the same group id and then have that number divided by the profit for the unique ID"

Either way just switch the divisor for what you want!

Also if you're using Postgresql >= 8.4 you can use a window function.

SELECT ID, GroupId, Profit, (Profit/ (sum(Profit) OVER(partition by GroupId)))*100 as pct
FROM core_dev.my_table as mt

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

One more option with window function

select ID, GroupID, Profit * 1. / SUM(profit) OVER(PARTITION BY GroupID)
from t1

Upvotes: 2

mvsagar
mvsagar

Reputation: 2079

An alternative solution using scalar sub-queries is as follows:

select t1.ID, t1.GroupID, (select sum(t2.Profit) * 1.0 / t1.Profit 
                           from table t2 
                           where t2.GroupID = t1.GroupID) as percentage_profit
from table t1;

Upvotes: 0

juergen d
juergen d

Reputation: 204854

select t1.ID, 
       t1. GroupID, 
       (t1.Profit * 1.0) / t2.grp_profit as percentage_profit
from table t1
inner join 
(
   select GroupID, sum(Profit) as grp_profit
   from table
   group by GroupID
) t2 on t1.groupid = t2.groupid

Upvotes: 3

Related Questions