473,325 Members | 2,442 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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

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?

Expand|Select|Wrap|Line Numbers
  1. SELECT [Total Employee per Zip].LocationId,
  2.    ([Total Employee per Zip].TotEmp)/SUM [Total Employee per Zip].EST
  3. FROM [Total Employee per Zip]
  4. GROUP BY [Total Employee per Zip].LocationId;
comes up with SYNTAX ERROR
Mar 20 '13 #1
2 1676
zmbd
5,501 Expert Mod 4TB
You surely didn't do this using the query designer :)

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

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    [Total Employee per Zip].LocationId,
  3.    (([Total Employee per Zip].TotEmp)
  4.        /Sum([Total Employee per Zip].EST))
  5.     AS EXP1
  6. FROM [Total Employee per Zip]
  7. GROUP BY [Total Employee per Zip].LocationId;
Mar 20 '13 #2
pod
298 100+
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
    Expand|Select|Wrap|Line Numbers
    1. SELECT [Total Employee per Zip].LocationId, 
    2.        SUM([Total Employee per Zip].EST) as SUM_EST
    3. FROM   [Total Employee per Zip]
    4. GROUP BY [Total Employee per Zip].LocationId
    5.  
  2. Below, the SUM query is inserted in another statement
    Expand|Select|Wrap|Line Numbers
    1. SELECT [Total Employee per Zip].LocationId, 
    2.        ([Total Employee per Zip].TotEmp)/[SUM_QUERY].SUM_EST
    3. FROM   [Total Employee per Zip], 
    4.        (
    5.         SELECT [Total Employee per Zip].LocationId, 
    6.                SUM([Total Employee per Zip].EST) as SUM_EST
    7.         FROM   [Total Employee per Zip]
    8.         GROUP BY [Total Employee per Zip].LocationId
    9.        ) as SUM_QUERY
    10. WHERE [Total Employee per Zip].LocationId = [SUM_QUERY].LocationId
    11.  


P:oD
Mar 20 '13 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: helpful sql | last post by:
Hi all, I am using an HTML table control on my ASP.Net page. Now I need to change this table so that columns become rows and rows become column. Since the table is very complex with many controls...
1
by: Leon | last post by:
Hi! Does anyone knows this problem? When I'm changing view to DS then I'm selecting fiew columns and I'm tying to "print selected records" I obtain different columns on my just printed page...
1
by: Leon | last post by:
Hello again! I post this message once again because I've noticed this kind of behavior in: A2k (mdb,adp) and RunTime. The problem is: When I'm changing view to DS then I'm selecting fiew...
2
by: Thomas Beyerlein | last post by:
Is it possible to have different columns of a datagrid bound to different datatables? I am working on a project and the specks have suddenly changed. Now a item can have multiple reports/...
1
by: dallasfreeman | last post by:
I'm looking at a quick way to get results that are displayed as rows to display as columns. I have three tables:- - The Questions for the survey - The Results of the survey (Columns are listed...
2
by: =?Utf-8?B?Z2FuZQ==?= | last post by:
Hi, In a gridview, How can i display different columns between item and edit modes. For eg. i have a sql that returns productname, categoryname, etc. In viewmode, i need to display only...
9
by: tabakaka | last post by:
i need to connect 2 different columns from 2 tables. note the these columns are strings (varchar) column on 1st table: description1 column on 2nd table: description2 example: description1:...
2
by: javakid | last post by:
Hi all I have results with different columns and i want to order the data according to different columns under different conditions. I have four columns: isflagged(T/F), FlaggedAt(Time),...
2
by: kirankumarn | last post by:
How to convert rows into different columns Thanks in advance
1
by: chopin | last post by:
I was wondering if there was a way to alternate data taken from two different columns in a query, then placing that data into a another query or table. For example, let's say I have the following...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.