SQL AVG of a SUM

Question

I want to get the average of a calculated sum. I have tried the syntax from this stackoverflow answer So my SQL query looks like this:

SELECT AVG(iq.stockvalue_sum), iq.date
FROM(
    SELECT CONCAT(DATE_FORMAT(s.date, '%Y'), '-01-01') as date,
    SUM(GREATEST(s.stockvalue,0)) as stockvalue_sum
    FROM stockvalues s
    GROUP BY CONCAT(DATE_FORMAT(s.date, '%Y'), '-01-01')
) iq

However this is not giving me a correct average. I want to get the average stockvalue for each year. The idea behind the table is to save every day the stock and stockvalue for each product. So this specifiq query is to show the average stockvalue for each year it has data for.

Edit: Sample output data

Stockvalue | Year
_________________
-  205     | 2015
-  300     | 2014

Input data:

pid | val | date
______________________
- 1 | 100 | 28-04-2015
- 2 | 150 | 28-04-2015
- 1 | 80  | 27-04-2015
- 2 | 80  | 27-04-2015
....
- 1 | 100  | 29-01-2014
- 2 | 100  | 29-01-2014
- 1 | 200  | 30-01-2014
- 2 | 200  | 30-01-2014

So I need to calculate know the average of the total stockvalue. So the sum of all stockvalues for day X and the average of X

Your outer query is missing a group by, but since the logical grouping column (`iq.date`) is the same as the inner query, it does not make much sense, i.e. your inner query is only going to return one result per year, so taking an average of this one row is pointless. As such I am a bit confused as to your requirements, perhaps some sample data and expected output would help? — GarethD, Apr 28 '15 at 14:06
That sample output is no help without sample input. My point was, I don't see why you need the `SUM` at all, can you not just do an average directly, so change `SUM(GREATEST(s.stockvalue,0))` to `AVG(GREATEST(s.stockvalue,0))`, and get rid of the outer query completely. — GarethD, Apr 28 '15 at 14:11
@BramDriesen -- your sample input doesn't match your desired results? For that specific input, are you just wanting 2015 has 125, 2014 has 50 and 2013 has 300? If so, no need to `sum` -- just use `avg`... — sgeddes, Apr 28 '15 at 14:21
@sgeddes let's take the year 2015 as example. The total stockvalue of `28-04` is 250 and the total for `27-04` is 160. I need the average of those two totals. So it would be a sum of all the values for date X and the average of all those sums. So the average would be `205` — Bram, Apr 28 '15 at 14:26

sgeddes · Answer 1 · 2015-04-28T14:12:12.490

At minimum you are missing a group by in your outer query:

SELECT AVG(iq.stockvalue_sum), iq.date
FROM(
    SELECT CONCAT(DATE_FORMAT(s.date, '%Y'), '-01-01') as date,
    SUM(GREATEST(s.stockvalue,0)) as stockvalue_sum
    FROM stockvalues s
    GROUP BY CONCAT(DATE_FORMAT(s.date, '%Y'), '-01-01')
) iq
GROUP BY iq.date

However, given your inner query is returning a single year with a summed value, the average of that value would be the same. Perhaps you can clarify your intentions. Are you sure you need the inner query at all? Perhaps this is all you need?

select avg(GREATEST(stockvalue,0)), CONCAT(DATE_FORMAT(s.date, '%Y'), '-01-01') as date
from stockvalues 
group by CONCAT(DATE_FORMAT(s.date, '%Y'), '-01-01')

Nope that's not working. I save the stockvalue for every product into the database. So to get the average for that day I need to do a sum of all the stockvalues. And the average of that. So to make things a bit more clear. I don't need to know the average stockvalue for the products. But for that day. — Bram, Apr 28 '15 at 14:22

score 1 · Accepted Answer · answered Apr 28 '15 at 14:39

1

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);

answered Apr 28 '15 at 14:39

GarethD

68,045
10
83
123

Yes, you are right, I had to group by date and then by year. Thank you! – Bram Apr 28 '15 at 16:45

SQL AVG of a SUM

2 Answers2