CODEWITHSUNDEEP
sqlms-access
vandelay
vandelay

Reputation: 2075

Percentage calculations with filters and column

I'm trying to create a new column in Microsoft Access/SQL but the new column should be calculated with a filtering.

If my table looks like below:

Name Value Type
A      5     X
A      4     I
A      3     P
A      1     B
B      1     X
B      8     I
B      7     P

Is it posisble to do a Group by Name and create a calculation like:

Value(Where Type=X) + Value(Where Type=I) / Sum(Value) ?

And return

Name CustomCol
A       0.64
B       0.56

Upvotes: 0

Views: 147

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

Yes, pretty much as you described:

select name,
       sum(iff(type in ("X", "I"), value, 0.0)) / sum(value)
from t
group by name;

Upvotes: 1

Related Questions