CODEWITHSUNDEEP
sqlms-accessms-access-2016
rob
rob

Reputation: 317

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

Upvotes: 2

Views: 1066

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 3

Applecore
Applecore

Reputation: 4099

I think that you will need to use a subquery to get the total for each SELECTION, which you can then use to calculate the percentage of each row:

SELECT 
    E.EVENT_ID, 
    E.EVENT_NAME, 
    E.EVENT_DATE, 
    E.SELECTION_NAME, 
    E.PPWAP,
    (SELECT SUM(E1.PPWAP) FROM tblEvent AS E1 WHERE E1.EVENT_ID=E.EVENT_ID AND E1.EVENT_DATE=E.EVENT_DATE) AS TOTAL_PPWAP,
    E.PPWAP/TOTAL_PPWAP*100 AS PCT_PPWAP
FROM tblEvent AS E

This produces the following output: enter image description here You may need to change the fields used in the WHERE part of the subquery depending on exactly how you define the total to be calculated.

Regards,

Upvotes: 2

Related Questions