SELECT Store, ROUND(AVG(Unemployment),2) AS avg_unempl, 
				ROUND(AVG(CPI),2) AS avg_CPI, 
				ROUND(AVG(Temperature),2) AS avg_temp
FROM PortfolioProjects..features
GROUP BY Store
ORDER BY Store
Store	avg_unempl avg_CPI avg_temp
1	7.44	217.27	66.91
2	7.4	216.92	66.73
3	7.01	220.69	70.39
4	5.65	129.2	61.42
5	6.16	217.84	68.22
6	6.41	218.84	68.5
SELECT  tra.Store, sto.Type, sto.Size,
				ROUND(SUM(CASE WHEN IsHoliday = 1 OR IsHoliday = 0
					THEN Weekly_Sales
					END),2) AS tot_sales,
				ROUND(SUM(CASE WHEN IsHoliday = 1
					THEN Weekly_Sales
					END),2) AS hol_sales,
				ROUND(SUM(CASE WHEN IsHoliday = 0
					THEN Weekly_Sales
					END),2) AS non_hol_sales
FROM PortfolioProjects..train tra, PortfolioProjects..stores sto
WHERE  tra.Store = sto.Store
GROUP BY tra.Store, sto.Size, sto.Type
Store	Type	Size	tot_sales	hol_sales	non_hol_sales
1	A	151315	222402808.85	16657476.56	205745332.29
2	A	202307	275382440.98	20792669	254589771.98
3	B	37392	57586735.07	4378110.5	53208624.57
4	A	205863	299543953.38	22431026.24	277112927.14
5	B	34875	45475688.9	3595016.07	41880672.83
6	A	202505	223756130.64	16809079.27	206947051.37

SELECT
  sto.Store,
  sto.Type,
  sto.Size,
  ROUND(SUM(CASE WHEN IsHoliday = 1 OR IsHoliday = 0
            THEN Weekly_Sales
            END), 2) AS tot_sales,
  ROUND(SUM(CASE WHEN IsHoliday = 1
            THEN Weekly_Sales
            END), 2) AS hol_sales,
  ROUND(SUM(CASE WHEN IsHoliday = 0
            THEN Weekly_Sales
            END), 2) AS non_hol_sales,
  f.avg_unempl,
  f.avg_CPI,
  f.avg_temp
FROM train tra
JOIN stores sto ON tra.Store = sto.Store
JOIN (
    SELECT
      f.Store,
      ROUND(AVG(f.Unemployment), 2) AS avg_unempl, 
      ROUND(AVG(f.CPI), 2) AS avg_CPI, 
      ROUND(AVG(f.Temperature), 2) AS avg_temp
    FROM features f
    GROUP BY f.Store
) f ON f.Store = sto.Store
GROUP BY
  sto.Store,
  sto.Size,
  sto.Type,
  f.avg_unempl,
  f.avg_CPI,
  f.avg_temp;