J.H J.H - 6 months ago 12
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
UNION
and
JOIN
. 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,
IIf(
0.9<Percentage_Variation_In_Total_MV AND
Percentage_Variation_In_Total_MV<1.1,'Pass','Fail') AS Result
FROM FebReport;

Answer

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:

SELECT
    [Current Market Value],
    [Previous Market Value],
    [Current Market Value] / [Previous Market Value] As Percentage_Variation
FROM
    (SELECT SUM([Market Value]) As [Current Market Value]  FROM FebReport) AS C,
    (SELECT SUM([Market Value]) As [Previous Market Value] FROM JanReport) AS P;