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
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
Last Comment
mcrmg
ASKER CERTIFIED SOLUTION
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.
ASKER
wow..thank you very much
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
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
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).
ASKER
thankyou