J.H J.H - 1 year ago 95
SQL Question

How do I divide the sum of one column by the sum of another column when they are from different tables in Access?

I want to grab the sum of two identically named columns, except they are from two different tables (I have been told to combine them multiple times, unfortunately I do not have a choice in the matter...). I want to then divide these two summed values to find the percentage variation.

Below is the code I wrote, all it does is load infinitely.

SELECT SUM(C.[Market Value]) AS CSUM,
SUM(P.[Market Value]) AS PSUM,
CSUM/PSUM AS Percentage_Variation
FROM JanReport AS P,
FebReport AS C;

I have been unsuccessful in using
. It seems like this function should be easily doable, as I have been able to sum and find % variation by asset class, I can even get the sum of both columns to show with the Totals function. However I just cannot seem to get them to divide.

Code that worked:

SELECT SUM(FebReport.[Market Value]) AS Curr_Total_MV,
SELECT SUM(JanReport.[Market Value]) FROM JanReport
) AS Prior_Total_MV,
SUM(FebReport.[Market Value]) /
SELECT SUM([Market Value]) FROM JanReport
) AS Percentage_Variation_In_Total_MV,
0.9<Percentage_Variation_In_Total_MV AND
Percentage_Variation_In_Total_MV<1.1,'Pass','Fail') AS Result
FROM FebReport;

Answer Source

Inline SELECT should work here:

SELECT SUM([Market Value]) / (SELECT SUM([Market Value]) FROM JanReport) AS Percentage_Variation, 
FROM FebReport;

Update: Two sub-queries each returning just one row can be cross-joined to provide the underlying values:

    [Current Market Value],
    [Previous Market Value],
    [Current Market Value] / [Previous Market Value] As Percentage_Variation
    (SELECT SUM([Market Value]) As [Current Market Value]  FROM FebReport) AS C,
    (SELECT SUM([Market Value]) As [Previous Market Value] FROM JanReport) AS P;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download