dividing the sum of two rows by the sums from the same rows different columns

[3mcfadyen]

This is the SQL I have written. I need to sum TotEmp #'s (with the same LocationId) and then divide that sum by the sum of EST
There are multiple lines with the same locationId, different TotEmp and different EST?

SELECT [Total Employee per Zip].LocationId,
   ([Total Employee per Zip].TotEmp)/SUM [Total Employee per Zip].EST
FROM [Total Employee per Zip]
GROUP BY [Total Employee per Zip].LocationId;

comes up with SYNTAX ERROR

[zmbd]

You surely didn't do this using the query designer :)

You are missing a few "()" and an "AS"

SELECT 
   [Total Employee per Zip].LocationId,
   (([Total Employee per Zip].TotEmp)
       /Sum([Total Employee per Zip].EST))
    AS EXP1
FROM [Total Employee per Zip]
GROUP BY [Total Employee per Zip].LocationId;

[pod]

I believe you're trying to use your aggregate function before it is completed, you must get the Sum before you can use it.
I am not certain of how the content of your table is structured, but this might get you closer to what you are trying to achieve... hope that helps

1. 
   You need to isolate your aggregate query from your main query. This query gets the SUM you need for your calculation
   

   SELECT [Total Employee per Zip].LocationId, 
          SUM([Total Employee per Zip].EST) as SUM_EST
   FROM   [Total Employee per Zip]
   GROUP BY [Total Employee per Zip].LocationId

2. Below, the SUM query is inserted in another statement
   

   SELECT [Total Employee per Zip].LocationId, 
          ([Total Employee per Zip].TotEmp)/[SUM_QUERY].SUM_EST
   FROM   [Total Employee per Zip], 
          (
           SELECT [Total Employee per Zip].LocationId, 
                  SUM([Total Employee per Zip].EST) as SUM_EST
           FROM   [Total Employee per Zip]
           GROUP BY [Total Employee per Zip].LocationId
          ) as SUM_QUERY
   WHERE [Total Employee per Zip].LocationId = [SUM_QUERY].LocationId

P:oD