Arian Arian - 1 year ago 45
SQL Question

How to do calculation based on most top row in a group

Please consider this Table:

StuffCode ClassCode Value
----------------------------------------
AA001 00000 32561
AA001 00001 1232
AA001 00002 12312
AB002 00000 2112
AB002 00001 1234
AB002 00002 120
AC003 00000 6986
AC003 00001 1423
AC003 00002 658


I want to Create Such this Result:

StuffCode ClassCode Percent
----------------------------------------
AA001 00000 100
AA001 00001 3.78
AA001 00002 37.81
AB002 00000 100
AB002 00001 58.42
AB002 00002 5.68
AC003 00000 100
AC003 00001 20.36
AC003 00002 9.41


That
Percent
Column claculated base on division of
Value
of each record by value of
Class Code = 00000
in each
StuffCode
group. For Example


For Row 2: (1232 / 32561) * 100 = 3.78

For Row 5: (1234 / 2112) * 100 = 58.42


...

How I can do this with one select?

Thanks

Answer Source

One way using Max() Over()

SELECT *,
       [Value] * 1.0 / Max(CASE
                       WHEN [ClassCode] = '00000' THEN [Value]
                     END) OVER(partition by StaffCode) * 100
FROM   Yourtable
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download