获取平均值总和的SQL查询

Question

需要使用SQL查询以获得以下结果

考虑表格

 Column1  || Column2 || Column3
    H1       A1          10
    H1       A1          10
    H1       A2          30
    H1       A2          30
    H1       A2          30

我希望查询列2的平均数，找到列1的总和。

例子

  Column1 || Column 3
      H1      40            (Average of A1 + A2 i.e (10 + 30)

- Vinod Kumar

---

你能提供一下你使用的数据库引擎标签吗？ - Leszek Mazur

"平均值"还是"总和"? - Gordon Linoff

表格中没有H2行？ - jarlh

Answer 1

SELECT column1, SUM(Avg_col3) AS total
FROM (
    SELECT column1, column2, AVG(column3) AS avg_col3
    FROM t
    GROUP BY column1, column2
) A
GROUP BY column1;

- VTi

---

和我五分钟前发布的几乎完全相同。 - Tim Biegeleisen

@Tim - 是的，你说得对。发帖后我才意识到这一点。既然你的回答是第一个，理想情况下应该被接受。你想让我删除我的回答吗？ - VTi

没关系，保持 :-) - Tim Biegeleisen

Answer 2

你可以通过两个聚合来完成这个操作：

SELECT Column1, SUM(Column3Avg) AS Column3
FROM
(
    SELECT Column1, Column2, AVG(Column3) AS Column3Avg
    FROM yourTable
    GROUP BY Column1, Column2
) t
GROUP BY
    Column1;

这种方法首先按照第一列和第二列进行聚合，生成一个中间结果，看起来像这样：

Column1 | Column2 | Column3Avg
H1      | A1      | 10
H1      | A2      | 30

然后，我们仅按第一列汇总上述内容，并对第三列平均值求和。

- Tim Biegeleisen

Answer 3

你的数据模型看起来非常混乱，如果有像这样重复的数字。我建议：

select column1, sum(column3)
from (select distinct column1, column2, column3
      from t
     ) t
group by column1;

- Gordon Linoff

Answer 4

对于 SQL Server，使用以下查询以获得所需结果。

WITH CTE AS (
    SELECT  Column1,
            AVG(Column3) AS Column3
    FROM Table1
    GROUP BY Column1, Column2
)
SELECT Column1, SUM(Column3) AS Column3
FROM CTE
GROUP BY Column1

- Karan

Answer 5

请运行以下代码。您将获得您的结果。

Select *
into #tmp
from (
    select 'h1' as Column1, 'A1' as Column2, 10 as Column3
    union all
    select 'h1' as Column1, 'A1' as Column2, 10 as Column3
    union all
    select 'h1' as Column1, 'A2' as Column2, 30 as Column3
    union all
    select 'h1' as Column1, 'A2' as Column2, 30 as Column3
    union all
    select 'h1' as Column1, 'A2' as Column2, 30 as Column3
)r
select * from #tmp
Select r.Column1, sum(r.Column3) Column3
from (
    select Column1, Column2, avg(Column3) Column3
    from #tmp
    group by Column1, Column2
 )r
 group by r.Column1

- R.Kabir