title: MS Access: Rank SUM() Values
body: <p>I am working on an old web app that is still using MS Access as it's data source and I have ran into issue while trying to rank SUM() values.</p>
  
  <p>Let's say I have 2 different account numbers each of those account numbers has an unknown number of invoices. I need to sum up the total of all the invoices, group it by account number then add a rank (1-2).</p>
  
  <p>RAW TABLE EXAMPLE...</p>
  
  <p>Account | Sales | Invoice Number<br />
  001     | 400   | 123<br />
  002     | 150   | 456<br />
  001     | 300   | 789<br /></p>
  
  <p>DESIRED RESULTS...</p>
  
  <p>Account | Sales | Rank<br />
  001     | 700   | 1<br />
  002     | 150   | 2<br /></p>
  
  <p>I tried...</p>
  
  <p>SELECT Account, SUM(Sales) AS Sales,
       (SELECT COUNT(*) FROM Invoices) AS RANK
  FROM Invoices
  ORDER BY Account</p>
  
  <p>But that query keeps returning the number of records assigned to that account and not a rank.</p>