Joining Multiple Select Statements in SQL

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
65
Good afternoon,

I have a table for GET_CONF and GET_CXL.
DateGET_CONFGET_CXL
01-Jan-2011
02-Jan-2012
03-Jan-2029
04-Jan-2054
05-Jan-2061
06-Jan-2049
07-Jan-2036
08-Jan-2056
09-Jan-20109
10-Jan-20138

I have calculated a 7-day moving average for GET_CONF using the following:

VBA Code:
SELECT o.Date, o.GET_CONF, o.GET_CXL
(
SELECT avg(GET_CONF)
from Output i
Where i.Date <= o.date
and i.date >= (o.date - 6)
having count (*) >=7
) AS GET_CONF_AVG
FROM [Output] AS o;
All i want to do is have the 7-day average for GET_CXL added to the table.
I assume I have to perform an inner-join, but I continue to get an error.
I simply want to use the same formula and add the GET_CXL portion.

Thanks,
Mike
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,670
Office Version
2013
Platform
Windows
Just offhand I copied your formula for CONF and changed CONF to CXL. Does that not work?
I also added a comma at the end of the first line as it seems that was missing.
Code:
SELECT o.Date, o.GET_CONF, o.GET_CXL,
(
SELECT avg(GET_CONF)
from Output i
Where i.Date <= o.date
and i.date >= (o.date - 6)
having count (*) >=7
) AS GET_CONF_AVG,
(
SELECT avg(GET_CXL)
from Output i
Where i.Date <= o.date
and i.date >= (o.date - 6)
having count (*) >=7
) AS GET_CXL_AVG
FROM [Output] AS o;
 

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
65
I did it multiple times and got a syntax error every time. However your way works just fine. Don't know what I did wrong.
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,361
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top