Answered by:
Use SubQuery to Get Percent of Total
Question

Sorry if this is a dumb question everyone, but I'm stuck on something that seems pretty simple.
If I create SQL like this:
Weights: [Volume]/2792
I get the row by row percent of the total.
However, there's no way I'm going to hardcode the total, so I really want to setup my query like this:
Weights: [Volume]/sum([Volume])
However, that gives me this.
Can someone show me how to dynamically sum the field 'Weights' so I can get the percent of each record?
Here is the SQL:
SELECT tblOffices.ServiceID, tblOffices.Branch, tblOffices.Volume, [Volume]/Sum([Volume]) AS Weights FROM tblOffices GROUP BY tblOffices.ServiceID, tblOffices.Branch, tblOffices.Volume;
There must be a way to do this using a SubQuery, but nothing that I have tried has worked.
Answers

Could you please clarify what you want your query to show? You're right that a subquery can give you the sum you want, but the SQL you posted doesn't make sense to me. It groups by ServiceID, Branch and Volume, but I would have thought you would want to show the total Volume for the ServiceID and Branch, which would mean summing the Volume. not grouping by it.
If what you want is to show the total Volume for the ServiceID and Branch, and show that total volume's proportion of the total, then this query ought to do it:
SELECT ServiceID, Branch, Sum(tblOffices.Volume) AS Volume, (Sum(tblOffices.Volume)/(SELECT Sum(Volume) FROM tblOffices)) AS Weights FROM tblOffices GROUP BY tblOffices.ServiceID, tblOffices.Branch
But I'm not sure if that's what you're after.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
 Edited by Dirk Goldgar Thursday, June 9, 2016 12:45 AM
 Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff, Moderator Thursday, June 9, 2016 5:17 AM
 Marked as answer by ryguy72 Thursday, June 9, 2016 12:08 PM

I tried several times to make this work and I kept using this:
Volume/Sum(Volume) FROM tblOffices
If the numerator and denominator are the same, it seems like it should result in 1:
(Sum(tblOffices.Volume)/(SELECT Sum(Volume) FROM tblOffices)
Nevertheless, somehow it works! I don't know why you need the sum in the numerator AND the denominator. That doesn't make any sense to me, but I suppose that's why it didn't work for me before. Can someone please explain that to me?
The numerator and denominator are not the same. The numerator, "Sum(tblOffices.Volume)", must be understood in terms of the grouping in the main query. That query groups by ServiceID and Branch, so the result of the aggregate function Sum(Volume) is the sum within the current combination of ServiceID and Branch.
The denominator, "(SELECT Sum(Volume) FROM tblOffices)" is a subquery that is independent of the main query's grouping. It simply returns the sum of Volume over the whole table.
Therefore, the expression "(Sum(tblOffices.Volume)/(SELECT Sum(Volume) FROM tblOffices))" divides the sum for the group by the sum for the whole table.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html Marked as answer by ryguy72 Thursday, June 9, 2016 2:47 PM
All replies

Could you please clarify what you want your query to show? You're right that a subquery can give you the sum you want, but the SQL you posted doesn't make sense to me. It groups by ServiceID, Branch and Volume, but I would have thought you would want to show the total Volume for the ServiceID and Branch, which would mean summing the Volume. not grouping by it.
If what you want is to show the total Volume for the ServiceID and Branch, and show that total volume's proportion of the total, then this query ought to do it:
SELECT ServiceID, Branch, Sum(tblOffices.Volume) AS Volume, (Sum(tblOffices.Volume)/(SELECT Sum(Volume) FROM tblOffices)) AS Weights FROM tblOffices GROUP BY tblOffices.ServiceID, tblOffices.Branch
But I'm not sure if that's what you're after.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
 Edited by Dirk Goldgar Thursday, June 9, 2016 12:45 AM
 Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff, Moderator Thursday, June 9, 2016 5:17 AM
 Marked as answer by ryguy72 Thursday, June 9, 2016 12:08 PM

Hi ryguy72,
please check the suggestion given by Dirk Goldgar MVP.
I think you can get your solution by his post.
if you think it worked for you please mark the suggestion as an answer.
if you still facing an issue let us know so that we can provide you further help.
Regards
Deepak
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey. 

I tried several times to make this work and I kept using this:
Volume/Sum(Volume) FROM tblOffices
If the numerator and denominator are the same, it seems like it should result in 1:
(Sum(tblOffices.Volume)/(SELECT Sum(Volume) FROM tblOffices)
Nevertheless, somehow it works! I don't know why you need the sum in the numerator AND the denominator. That doesn't make any sense to me, but I suppose that's why it didn't work for me before. Can someone please explain that to me?
Thanks again!!
 Edited by ryguy72 Thursday, June 9, 2016 1:53 PM

I tried several times to make this work and I kept using this:
Volume/Sum(Volume) FROM tblOffices
If the numerator and denominator are the same, it seems like it should result in 1:
(Sum(tblOffices.Volume)/(SELECT Sum(Volume) FROM tblOffices)
Nevertheless, somehow it works! I don't know why you need the sum in the numerator AND the denominator. That doesn't make any sense to me, but I suppose that's why it didn't work for me before. Can someone please explain that to me?
The numerator and denominator are not the same. The numerator, "Sum(tblOffices.Volume)", must be understood in terms of the grouping in the main query. That query groups by ServiceID and Branch, so the result of the aggregate function Sum(Volume) is the sum within the current combination of ServiceID and Branch.
The denominator, "(SELECT Sum(Volume) FROM tblOffices)" is a subquery that is independent of the main query's grouping. It simply returns the sum of Volume over the whole table.
Therefore, the expression "(Sum(tblOffices.Volume)/(SELECT Sum(Volume) FROM tblOffices))" divides the sum for the group by the sum for the whole table.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html Marked as answer by ryguy72 Thursday, June 9, 2016 2:47 PM
