Search code examples
sqlms-accessms-access-2016

Calculate Percentage of Total by Group in Access 2016

I have a query that returns results for multiple EVENT_IDs. I would like to calculate the percentage PPWAP of the total PPWAP of each EVENT_ID for each SELECTION-ID.

This is an example of the results for a single EVENT_ID:

enter image description here

Can anyone suggest how this can be done?

Thanks

Solution

  • I would be inclined to use GROUP BY and JOIN for this. Your question is a bit unclear. If you want the proportion by event:

    select e.*, PPWAP / total_PPWAP as ratio
from tblEvent as e inner join
     (select event_id, sum(PPWAP) as total_PPWAP
      from tblEvent 
      group by event_id
     ) as ee
     on ee.event_id = e.event_id;

    If you really want it by some unseen column such as selection_id, then you would use that for the GROUP BY and JOIN.

    I often like correlated subqueries. But in this case, it likely does more work than necessary, because it does the aggregation for each group multiple times.