Avatar of mcrmg
mcrmg
 asked on

SQL pivot query

Hi,

is there a way to achieve this?  thx


SOURCE TABLE
State      Amount
NY      10
NY      20
NY      30
CA      50
NY      40
NJ      80
VA      10
NJ      20
NJ      30

OUTCOME  (only 3 state are needed, but the % is the sum of all amount
STATE   COUNT    AMOUNT         %
NY          4                  100               100/290=0.34
NJ           3                   130               0.44
VA          1                   10                  0.03
Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
mcrmg

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Steve Wales

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
mcrmg

ASKER
wow..thank you very much
mcrmg

ASKER
I forgot a question, please let me know if a new q is needed.  

is there a way to show in this way sorted?  The range is fixed.

state Row_Count   Amount      pct
----- ----------- ----------- ---------
0-0.03     1           10          0.03
0.1-0.4    4           100         0.34
0.4-0.5    3           130         0.45

thanks
Steve Wales

You may want to post it as a new question, just because since this one is closed it's not going to be very visible to other experts who may have different / better solutions (but I'd refer back to this one for the ground work that's already been done).
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
mcrmg

ASKER
thankyou