Joining Multiple Select Statements in SQL

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
70
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,826
Office Version
  1. 2019
Platform
  1. 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
70
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
 

roundcubefour

New Member
Joined
Sep 5, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Each SELECT statement within UNION must have the same number of columns.
The columns must also have similar data types.
The columns in each SELECT statement must also be in the same order.
 

roundcubefour

New Member
Joined
Sep 5, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
SELECT t1.ks, t1.[# Tasks], COALESCE(t2.[# Late], 0) AS [# Late]
FROM
(SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1
LEFT JOIN
(SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2
ON (t1.ks = t2.ks); shareit
 

Watch MrExcel Video

Forum statistics

Threads
1,129,312
Messages
5,635,491
Members
416,860
Latest member
coen078

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top