英文:
Combine the results one SQL query to the results of another SQL query to make one table
问题
我正在使用SQL Server，我有一些单独工作的查询，但我想将它们合并到一个表中。
我尝试将第一个查询与第二个连接，但我不确定正确的语法。
第一个查询：
SELECT Store, ROUND(AVG(Unemployment),2) AS 平均失业率,
ROUND(AVG(CPI),2) AS 平均CPI,
ROUND(AVG(Temperature),2) AS 平均温度
FROM PortfolioProjects..features
GROUP BY Store
ORDER BY Store
第二个查询：
SELECT tra.Store, sto.Type, sto.Size,
ROUND(SUM(CASE WHEN IsHoliday = 1 OR IsHoliday = 0
THEN Weekly_Sales
END),2) AS 总销售额,
ROUND(SUM(CASE WHEN IsHoliday = 1
THEN Weekly_Sales
END),2) AS 假日销售额,
ROUND(SUM(CASE WHEN IsHoliday = 0
THEN Weekly_Sales
END),2) AS 非假日销售额
FROM PortfolioProjects..train tra, PortfolioProjects..stores sto
WHERE tra.Store = sto.Store
GROUP BY tra.Store, sto.Size, sto.Type
请注意，这只是查询的中文翻译部分，没有其他内容。
英文:
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
答案1
得分: 1
将第一个查询的分组结果加入，并将这些列添加到 GROUP BY 中。您可以选择先对这两个查询进行分组，然后再进行连接，因为两者实质上只按独特的 Store 进行分组。
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论