Avg of a Sum in One Query

Avg of a Sum in one query

I think your question needs a bit of explanation. If you want to take the sums grouped by t.client you can use:

SELECT t.client, SUM(t.asset)
FROM the-table t
GROUP BY t.client

Then, if you want to take the average of this sume, just make:

SELECT AVG(asset_sums)
FROM
(
    SELECT t.client, SUM(t.asset) AS asset_sums
    FROM the-table t
    GROUP BY t.client
) as inner_query

You can't however group the outer query, because this will give you results like in the first query. The results from the inner query are already grouped by t.client.

SQL AVG of a SUM

I think you need to group your inner query by date, and your outer query by year to get the results you are after:

SELECT  AVG(s.Stockvalue) AS Stockvalue
        YEAR(s.Date) AS Date
FROM    (   
            SELECT  DATE(s.Date) AS Date, 
                    SUM(GREATEST(s.stockvalue,0)) AS Stockvalue 
            FROM    stockvalues AS s 
            GROUP BY DATE(s.Date)
        ) AS s
GROUP BY YEAR(s.Date);

Find sum and average in a single query

you can use the following

SELECT m.col as SUM, m.col/5 as AVG  
FROM (SELECT (a + b - c * d) col FROM master) m ;

MAX, SUM then AVG all in one SQL query?

You can just use subqueries:

SELECT uid, SUM(MaxScore), Avg(MaxScore) 
FROM
(
  SELECT uid, lesson_id,max(score) as MaxScore
  FROM scores 
  GROUP BY userid, lesson_id 

) AS m
GROUP BY  uid

or for just one user:

SELECT SUM(MaxScore), Avg(MaxScore) 
FROM
(
  SELECT lesson_id,max(score) as MaxScore
  FROM scores 
  WHERE uid = $uid
  GROUP BY lesson_id 
) AS m

or for "a bunch of specific users":

$uidlist = (comma-delimited list of user ids)

SELECT uid, SUM(MaxScore), Avg(MaxScore) 
FROM
(
  SELECT uid, lesson_id,max(score) AS MaxScore
  FROM scores 
  WHERE FIND_IN_SET(uid, $uidlist)
  GROUP BY uid, lesson_id 
) AS m
GROUP BY uid

Select sum of CreationUtcTime , Avg time and count between speed change from 0 in sql query

SELECT CreationUtcTime, Speed, convert(varchar,(CreationUtcTime - LAG(CreationUtcTime) OVER (ORDER BY CreationUtcTime)),108) AS diff     
FROM assetstatusrecords
WHERE Speed <> 0.00
ORDER BY CreationUtcTime

will give the sum of the time and then you can proceed with count and average of the data easily.

---

Related Topics