-2

I'm using SQL Server and I have a couple of queries that work individually, but I would like to combine them together into one table.

I've tried to take the first query and join it with the 2nd, but I'm not sure of the right syntax.

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
LuLuGaGa
  • 13,089
  • 6
  • 49
  • 57
Darnell
  • 1
  • 1
  • Your edits made the formatting worse, so I've rolled them back. Do not paste images of code or data, only paste as text – Charlieface May 28 '23 at 20:16
  • Consider using the standard, modern explicit join not outdated [implicit join](https://stackoverflow.com/q/44917/1422451) as you do in second query. – Parfait May 28 '23 at 23:23
  • "combine them together into one table" doesn't mean anything in particular. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. Debug questions require a [mre]. [ask] [Help] Code that does not do what one wishes it did does not communicate the doing of what one wishes it did. – philipxy May 29 '23 at 02:23

1 Answers1

1

Just join the grouped results of the first query, and then add those columns to the GROUP BY. You could alternatively group up both queries first and join themm afterwards, as both are essentially grouped only by unique Store.

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;
Charlieface
  • 52,284
  • 6
  • 19
  • 43