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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER