Select results from multiple subqueries

Question

I am trying to make a single table from the results of 9 different subqueries. Here is my code:

SELECT

APR16
FROM(
SELECT Sum(APR.[kwh]) AS APR16
FROM Peak_Times, Meter_Buckets INNER JOIN APR ON Meter_Buckets.METER = APR.meter
WHERE (((APR.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND APR.kwh IS NOT NULL)
,

MAY16
FROM(
SELECT Sum(MAY.[kwh]) AS MAY16
FROM Peak_Times, Meter_Buckets INNER JOIN MAY ON Meter_Buckets.METER = MAY.meter
WHERE (((MAY.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND MAY.kwh IS NOT NULL)
,

JUN16
FROM(
SELECT Sum(JUN.[kwh]) AS JUN16
FROM Peak_Times, Meter_Buckets INNER JOIN JUN ON Meter_Buckets.METER = JUN.meter
WHERE (((JUN.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND JUN.kwh IS NOT NULL)
,

JUL16
FROM(
SELECT Sum(JUL.[kwh]) AS JUL16
FROM Peak_Times, Meter_Buckets INNER JOIN JUL ON Meter_Buckets.METER = JUL.meter
WHERE (((JUL.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND JUL.kwh IS NOT NULL)
,

AUG16
FROM(
SELECT Sum(AUG.[kwh]) AS AUG16
FROM Peak_Times, Meter_Buckets INNER JOIN AUG ON Meter_Buckets.METER = AUG.meter
WHERE (((AUG.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND AUG.kwh IS NOT NULL)
,

SEP16
FROM(
SELECT Sum(SEP.[kwh]) AS SEP16
FROM Peak_Times, Meter_Buckets INNER JOIN SEP ON Meter_Buckets.METER = SEP.meter
WHERE (((SEP.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND SEP.kwh IS NOT NULL)
,

OCT16
FROM(
SELECT Sum(OCT.[kwh]) AS OCT16
FROM Peak_Times, Meter_Buckets INNER JOIN OCT ON Meter_Buckets.METER = OCT.meter
WHERE (((OCT.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND OCT.kwh IS NOT NULL)
,

NOV16
FROM(
SELECT Sum(NOV.[kwh]) AS NOV16
FROM Peak_Times, Meter_Buckets INNER JOIN NOV ON Meter_Buckets.METER = NOV.meter
WHERE (((NOV.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND NOV.kwh IS NOT NULL)
,

DEC16
FROM(
SELECT Sum(DEC.[kwh]) AS DEC16
FROM Peak_Times, Meter_Buckets INNER JOIN DEC ON Meter_Buckets.METER = DEC.meter
WHERE (((DEC.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND DEC.kwh IS NOT NULL)

If I do just the first subquery,

SELECT

APR16
FROM(
SELECT Sum(APR.[kwh]) AS APR16
FROM Peak_Times, Meter_Buckets INNER JOIN APR ON Meter_Buckets.METER = APR.meter
WHERE (((APR.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND APR.kwh IS NOT NULL);

it returns a result with no errors. However when I attempt to chain them all together like above, it gives me a syntax error on the 2nd FROM. I want a result that looks similar to this:

APR16|MAY16|JUN16
57212|45681|721

Can anyone tell me the correct syntax to do this? This may be something simple that I have overlooked or it could be something I just can't do. Thank you in advance for the help.

score 4 · Accepted Answer · answered Feb 08 '17 at 20:28

You can get the results for multiple sub-queries in the format you are looking for like this:

select
    (select count(*) from table1) as result1,
    (select count(*) from table2) as result2,
    ..

Applied to your query, it would look something like this:

SELECT
    (SELECT Sum(APR.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN APR ON Meter_Buckets.METER = APR.meter
    WHERE (((APR.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND APR.kwh IS NOT NULL)
     AS APR16
    ,
    (SELECT Sum(MAY.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN MAY ON Meter_Buckets.METER = MAY.meter
    WHERE (((MAY.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND MAY.kwh IS NOT NULL)
     AS MAY16
    ,
    (SELECT Sum(JUN.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN JUN ON Meter_Buckets.METER = JUN.meter
    WHERE (((JUN.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND JUN.kwh IS NOT NULL)
     AS JUN16
    ,
    (SELECT Sum(JUL.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN JUL ON Meter_Buckets.METER = JUL.meter
    WHERE (((JUL.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND JUL.kwh IS NOT NULL)
     AS JUL16
    ,
    (SELECT Sum(AUG.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN AUG ON Meter_Buckets.METER = AUG.meter
    WHERE (((AUG.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND AUG.kwh IS NOT NULL)
     AS AUG16
    ,
    (SELECT Sum(SEP.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN SEP ON Meter_Buckets.METER = SEP.meter
    WHERE (((SEP.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND SEP.kwh IS NOT NULL)
     AS SEP16
    ,
    (SELECT Sum(OCT.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN OCT ON Meter_Buckets.METER = OCT.meter
    WHERE (((OCT.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND OCT.kwh IS NOT NULL)
     AS OCT16
    ,
    (SELECT Sum(NOV.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN NOV ON Meter_Buckets.METER = NOV.meter
    WHERE (((NOV.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND NOV.kwh IS NOT NULL)
     AS NOV16
    ,
    (SELECT Sum(DEC.[kwh])
    FROM Peak_Times, Meter_Buckets INNER JOIN DEC ON Meter_Buckets.METER = DEC.meter
    WHERE (((DEC.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND DEC.kwh IS NOT NULL)
     AS DEC16

This worked wonderfully, I just had to change the SELECT at the beginning to say SELECT * FROM because Access was telling me I had to have a table in my input. Thank you very much! — Dustin Knight, Feb 08 '17 at 20:35
Interesting... You must be really using Access (I had mistakenly removed that tag, but re-added it). With SQL Server and MySQL the syntax I suggested should be supported, but I guess Access behaves slightly different. — dana, Feb 08 '17 at 21:31
That solution does not work for oracle databases, and it returns: "FROM keyword not found where expected" — Pepe Alvarez, Nov 26 '21 at 19:56

score 1 · Answer 2 · answered Feb 08 '17 at 20:29

Something like this might work:

WITH
APR16 as ( select ... apr16 from ...),
MAY16 as ( select ... may16 from ...),
JUN16 as ( select ... jun16 from ...)
select apr16,may16,jun16 from APR16, MAY16, JUN16;

However, it's not the most elegant solution to this pivoting problem I think. Some databases support pivoting results, I'm not sure if ms-access-2016 is among those (also I'm not sure it supports WITH clauses).

score 1 · Answer 3 · answered Feb 08 '17 at 21:24

Try something like this

    SELECT a.AUG16, b.JUN16
    FROM
    (SELECT Sum(AUG.[kwh]) AS AUG16
    FROM Peak_Times, Meter_Buckets INNER JOIN AUG ON Meter_Buckets.METER = AUG.meter
    WHERE (((AUG.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND AUG.kwh IS NOT NULL)
) a
,
    (FROM(
    SELECT Sum(JUN.[kwh]) AS JUN16
    FROM Peak_Times, Meter_Buckets INNER JOIN JUN ON Meter_Buckets.METER = JUN.meter
    WHERE (((JUN.strdatetime)=[Peak_Times].[strdatetime]) AND ((Meter_Buckets.BUCKET)=1)) AND JUN.kwh IS NOT NULL)
) b

Select results from multiple subqueries

3 Answers3