April 21, 2011 at 6:03 am
Hi,
I have this table called Transactions structured like this:
Payer_ID_________Transaction_type__________Amount
AAA_________________d_____________________100
AAA_________________d_____________________500
BBB_________________d_____________________600
BBB_________________c_____________________800
BBB_________________a_____________________700
CCC_________________x_____________________100
DDD_________________x_____________________900
DDD_________________d_____________________900
What I would like to do is make a query to Count the Transaction_type as Volume and the Amount as Value, then group them by Payer_ID and then calculate the percentage of the total for both the Count of transaction type and Sum of Value.
This should look like:
Payer_ID_____________Volume__________Value____Share volume_____Share value
AAA__________________2_______________600_________25%_____________13%
BBB__________________3_______________2100________37.5%____________45%
CCC__________________1________________100________12.5%____________2%
DDD__________________2_______________1800_________25%____________39%
Any help would be very much appreciated.
April 21, 2011 at 7:54 am
I managed to come up with a solution to my own problem. I'll post it anyhow, maibe somebody else could use it:
SELECT a.Payer_ID, Count(a.Transaction_type) AS Volume, Sum(a.Amount) AS Value, [Volume]/
(
Select Count(Transaction_type) as b FROM Transactions
) AS [Share volume]
, [Value]/
(
Select Sum(Amount) as c FROM Transactions
) AS [Share value]
FROM Transactions AS a
GROUP BY a.Payer_ID
ORDER BY Count(a.Transaction_type) DESC;
If anyone thinks this can be improved, I'm opened to suggestions.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy