MS Access SQL - Calculate shares for volume and value in a query

  • 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.

  • 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