none
Use SubQuery to Get Percent of Total RRS feed

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

    enter image description here

    However, there's no way I'm going to hard-code the total, so I really want to setup my query like this:

    Weights: [Volume]/sum([Volume])
    

    However, that gives me this.

    enter image description here

    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.


    MY BOOK

    Wednesday, June 8, 2016 6:01 PM

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


    Thursday, June 9, 2016 12:45 AM
  • 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
    Thursday, June 9, 2016 2:41 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


    Thursday, June 9, 2016 12:45 AM
  • 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.

    Thursday, June 9, 2016 5:17 AM
    Moderator
  • This works perfect.  I guess that's why you are an MVP. 

    Thanks!


    MY BOOK

    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?

    Thanks again!!


    MY BOOK


    • Edited by ryguy72 Thursday, June 9, 2016 1:53 PM
    Thursday, June 9, 2016 12:12 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
    Thursday, June 9, 2016 2:41 PM
  • Oh, that's the SubQuery part.  Got it.  I have tried to understand SubQueries for a few years.  I understand the concept, but I was never, never, never good at those things.

    Thanks again!


    MY BOOK

    Thursday, June 9, 2016 2:47 PM