CODEWITHSUNDEEP
mysqlgroup-byaggregate-functions
cyphos
cyphos

Reputation: 51

MySQL - How to the AVG ON SUM?

I want to calculate the average on the sum of every row but I didn't succeed. Also, I ask you for helping to me.

id   sessionId   duration
1    5           10
2    5           12
3    14          5
4    14          5
5    21          7
6    21          12

Result:

sessionId    sum
5            22
14           10
21           19

2nd result sum:

  22+10+19 = 51

Average

51/3 = 17

This i my sql:

SELECT `SessionID`
     , SUM(`Duration (s)`) AS SOMME_total 
     , SEC_TO_TIME(SUM(`Duration (s)`)) AS SOMME_HEURE 
  FROM 
     ( SELECT SUM(i2.`Duration (s)`) AS SOMME_ROW 
         FROM issueNubitalk i2 
        WHERE i2.Campaign LIKE 'Canal%' 
        GROUP 
           BY i2.SessionID
     )

Upvotes: 0

Views: 851

Answers (4)

ScaisEdge
ScaisEdge

Reputation: 133380

You could calc the avg fo the result 

select avg(T.SOMME_total) 
from (
  SELECT `SessionID`
    , SUM(`Duration (s)`) AS SOMME_total 
    , SEC_TO_TIME(SUM(`Duration (s)`)) AS SOMME_HEURE 
  FROM FROM issueNubitalk i2 
      WHERE i2.`Campaign` 
      LIKE 'Canal%' 
  GROUP by i2.`SessionID`
) T

Upvotes: 1

Newton Kumar
Newton Kumar

Reputation: 350

You can use below query to get result as you need its written in PostgreSQL and you can convery it in MYSQL easily.

SELECT
  average_value.totalSum/average_value.totalCount AS averageValue
FROM
(
  SELECT
   count(*) as totalCount,
   sum(total_value.sum) as totalSum
  FROM (
     SELECT
       i2.SessionID,
       sum(i2.Duration) as sum
     FROM issueNubitalk i2
     WHERE
      i2.`Campaign` LIKE 'Canal%' 
     GROUP BY i2.SessionID
  ) total_value
  ) average_value;

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272236

You can divide the SUM by distinct COUNT:

SELECT SUM(duration) / COUNT(DISTINCT sessionId)
FROM t

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

Use a subquery:

SELECT AVG(duration)
FROM
(
    SELECT SUM(duration) AS duration
    FROM issueNubitalk
    GROUP BY sessionId
) t;

I am using the column names from your sample data, which don't appear to line up entirely with your query. But the above answer would probably be more beneficial to future readers of this question anyway.

Upvotes: 1

Related Questions