Saturday, April 06, 2013

Using calculations in SQL statements

I've not done much training with Microsoft Access, so I've been learning by doing and trying, for the most part. This week, I figured out that you can do a calculation in SELECT statement, like to figure out the percentage of records in your selection. Up to now, I've pulled grouped results into Excel and did my calculations there.

I would think there would be an even easier way to build this, but ... maybe there is if I knew how to use the Query Wizard instead of writing my own SQL.

SELECT AASStatus, COUNT(*) AS [COUNT], FORMAT((COUNT(AASStatus) / (SELECT COUNT(*) FROM [DC-Match-N])),"Percent") AS [Percent]
FROM [DC-Match-N]

So, my table name (actually a query) is [DC-Match-N], and I want to group the all the records by their AASStatus, count the records and get the percentage. Probably my most common goal is to find out how many records meet a certain search criteria.

Breaking down the SELECT LINE:

  • First is the field I want to count.
  • Next is the count of records for that row. Since I GROUP BY AASStatus later, it will count the records that match each status. I aliased this column as [Count]
  • Next is where we do the percentage:
    • FORMAT allows me to format a result as a percentage, currency, etc. I learned about it in this post on (I actually did this first by doing my own *100 and using the ROUND function, but found the FORMAT function later.
    • Inside of the FORMAT tag is the math. Put simply is it "[part] / [whole]" formatted as a percent.
      • The [part] is the COUNT(AASStatus), or the count of the records that matches the AASStatus for that row.
      • The [whole] is a new SELECT statement to get the count of all records from the [DC-Match-N] query.
      • The percentage part is handled by the FORMAT function above.

Here's what it looks like:

AASStatus Count Percent
Status 1 136 4.63%
Status 2 39 1.33%
Status 3 5 0.17%
Status 4 190 6.46%
Status 5 2569 87.41%

This seems so simple ... to do the percentage as part of the select, but I hadn't thought of trying it before. 


Unknown 11:42 AM  

It has been a couple of years since I developed anything with the Access query wizard (which is a fabulous way to LEARN sql, btw), but I think that if you try right-clicking in the field-name space at the top of a wizard column, you might get the Access formula builder, which will let you type out your formula. You will want to give it a name; I think the Wizard converts Name:[field]*6 to ([field]*6) as Name. The Access help files are excellent, so you'll be up and running in minutes.

  © Blogger template 'A Click Apart' by 2008

Back to TOP