0

I have the following query:

SELECT [3-2017].[Dealer#], Sum([3-2017].Balance) AS SumOfBalance
FROM [3-2017]
GROUP BY [3-2017].[Dealer#];

I want to add a column that returns the percent of grand total for each dealer#. I think I need another single-row query with the totals and join or union somehow, but I'm completely lost.

I've looked through a few different posts and this is the top result, but it doesn't solve my problem, unfortunately.

Community
  • 1
  • 1
Ron L
  • 51
  • 3
  • 11

1 Answers1

0

Really best and easiest done in a report. However, try:

SELECT [3-2017].[Dealer#], Sum([3-2017].Balance) AS SumDealerBal,    
(SELECT Sum(Balance) FROM [3-2017]) AS SumBalance, SumDealerBal/SumBalance AS DealerPct   
FROM [3-2017]  
GROUP BY [3-2017].[Dealer#];

Advise no spaces or special character/punctuation (underscore is only exception) in any names. Better would be DealerNum or Dealer_Num.

June7
  • 19,874
  • 8
  • 24
  • 34
  • You're right, I do need to change the name. I did try that, and it did work; however, when I applied the filter 'HAVING [3-2017].[Dealer#] >=600', the calculation 'SumBalance' did not adjust. – Ron L Apr 07 '17 at 18:15
  • Did you apply filter to the inner query? – June7 Apr 07 '17 at 20:12