SQL query to get sum of averages

user_5765693 0
Viewed 95

Need to SQL QUERY to get following result

Consider Table

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

I want query to find Sum of Column1 for the averages of column2

Example

  Column1 || Column 3
      H1      40            (Average of A1 + A2 i.e (10 + 30)
sql
5 Answers
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;
user_115041280

You may do this via two aggregations:

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

This approach first aggregates by both the first and second column, to generate an intermediate result looking like:

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

Then, we aggregate the above by the first column alone, and sum the column 3 averages.

user_18632290

Your data model looks really messed up, if you have numbers repeating like this. I would suggest:

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

For SQL Server use following query to get desired result.

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
user_96952860

Please run the below code. You will get your result.

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
user_98274530
Related