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

• mirtomi

  Valued Member

  Points: 52

  More actions

  April 21, 2011 at 6:03 am

  #389345

  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.

• mirtomi

  Valued Member

  Points: 52

  More actions

  April 21, 2011 at 7:54 am

  #1314526

  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.