Join 3,564 readers in helping fund MetaFilter (Hide)


Need help with SQL statement
January 26, 2011 7:13 AM   Subscribe

I need to create a query in Access that shows the percentage of a total. I can't grasp the SQL statement I need.

Let's say I have many companies, and each of those companies has a number of orders. Each company only has one location. I need something that shows the number of companies and % of orders completed at each location, like so:

Location | Number of Companies | Orders completed
Tokyo | 15 | 60%
Paris | 20 | 75%

Here is my db structure:

tbl_locations
LocationID
Location

tbl_companies
CompanyID
CompanyName
LocationID

tbl_orders
OrderID
CompanyID
Completed (yes/no)

This is a similar scenario but the SQL doesn't work (I get a syntax error on the join statement and I have spent an hour retyping it and searching for rogue commas).
posted by desjardins to Computers & Internet (7 answers total) 2 users marked this as a favorite
 
Try this:
SELECT tbl_locations.LocationID,
  SUM(IIf(Completed=-1,1,0)) AS CompletedOrders,
  COUNT(*) AS AllOrders,
  SUM(IIf(Completed=-1,1,0))/COUNT(*) AS Percentage
FROM ((tbl_locations LEFT JOIN tbl_companies ON tbl_locations.LocationID = tbl_companies.LocationID)
  LEFT JOIN tbl_orders ON tbl_companies.CompanyID = tbl_orders.CompanyID)
GROUP BY tbl_locations.LocationID

posted by Doofus Magoo at 7:50 AM on January 26, 2011


Note that the third column isn't technically necessary (it's the total number of orders per location), but I included it for maximum transparency. The fourth column is just column 2 divided by column 3.
posted by Doofus Magoo at 7:51 AM on January 26, 2011


... and actually, you'd want to multiply the last column by 100 and round it (and append the percent sign) to get it exactly how you describe:
ROUND(100*SUM(IIf(Completed=-1,1,0))/COUNT(*)) & '%' AS Percentage

posted by Doofus Magoo at 7:54 AM on January 26, 2011


Whoops, I misread your question. I thought you wanted the total number of completed orders in the second column, but you wanted the number of companies.
posted by Doofus Magoo at 7:58 AM on January 26, 2011


Here's a new version that should give you what you need. I added the number of companies per location as a subquery to try to maintain some degree of continuity with my previous responses, as well as (at least some) readability.

Note that what we're doing here is making two ad hoc tables: ... and then joining those two tables together (line 10):
SELECT a.Location, b.LocationCount, a.Percentage FROM
(
SELECT tbl_locations.LocationID, Location,
  SUM(IIf(Completed=-1,1,0)) AS CompletedOrders,
  COUNT(*) AS AllOrders,
  ROUND(100*SUM(IIf(Completed=-1,1,0))/COUNT(*)) & '%' AS Percentage
FROM ((tbl_locations LEFT JOIN tbl_companies ON tbl_locations.LocationID = tbl_companies.LocationID)
  LEFT JOIN tbl_orders ON tbl_companies.CompanyID = tbl_orders.CompanyID)
GROUP BY tbl_locations.LocationID, Location
) a LEFT JOIN
(
SELECT LocationID, COUNT(*) AS LocationCount
FROM tbl_companies
GROUP BY LocationID
) b ON a.LocationID = b.LocationID

posted by Doofus Magoo at 8:04 AM on January 26, 2011


Note that if you were using a real database, you could have just added a COUNT(DISTINCT CompanyID) to the original query to add a column for the number of companies. Access, however, doesn't support COUNT(DISTINCT fieldname).

I'll stop spamming the thread now.

posted by Doofus Magoo at 8:07 AM on January 26, 2011


Wow, I owe you a beer. If we're all ever in the same city at the same time, I'll take you and FishBike out for drinks because of his help on my previous question.
posted by desjardins at 8:21 AM on January 26, 2011


« Older I'm curious about the custom i...   |  Want to leave for a new job, d... Newer »
This thread is closed to new comments.