Joining Multiple Select Statements in SQL

[MCTampa]

Good afternoon,

I have a table for GET_CONF and GET_CXL.

Date	GET_CONF	GET_CXL
01-Jan-20	1	1
02-Jan-20	1	2
03-Jan-20	2	9
04-Jan-20	5	4
05-Jan-20	6	1
06-Jan-20	4	9
07-Jan-20	3	6
08-Jan-20	5	6
09-Jan-20	10	9
10-Jan-20	13	8

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

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

 

[xenou]

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.

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]

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]

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]

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