CODEWITHSUNDEEP
mysqlsql
RvdK
RvdK

Reputation: 19790

Sql query to extract average grouped by a column

I'm trying to generate a SQL query to extract an average montly powerusage (of a year) for an ID.

+----+------------+------------+
| id | powerusage |    date    |
+----+------------+------------+
|  1 |        750 | 2011-12-2  |
|  1 |       1000 | 2011-12-1  |
|  1 |       1500 | 2011-11-15 |
|  1 |        100 | 2011-11-13 |
|  1 |         50 | 2011-11-10 |
|  2 |        500 | 2011-11-15 |
|  2 |        200 | 2011-11-13 |
+----+------------+------------+

So if ID = 1 I want (avg november + avg december) / 2 = (1750/2 + 1650/3) / 2 = 712.5

select AVG(powerusage) as avgMontlyPowerUsage
from usagetable 
where id = 1 and YEAR(date) = 2011

But this will give me 680.

How do I do a average on a group?

Many thanks for all the answers! But I see my question is incorrect. See updated question

Upvotes: 1

Views: 2642

Answers (5)

Kevin Coulombe
Kevin Coulombe

Reputation: 1575

This should give you monthly averages for every year and user. Some of the syntax may be MS SQL specific, but the logic should be good.

SELECT id, AVG(usage), year FROM
(SELECT id, SUM(powerusage) as usage, YEAR(date) as Year, MONTH(date) as Month
  FROM usagetable 
  GROUP BY id, YEAR(date), MONTH(date)) as InnerTable
GROUP BY id, year

Upvotes: 1

Jaydee
Jaydee

Reputation: 4158

Something like

select AVG(montlyPowerUsage) from (

SELECT MONTH(date) as mnth,sum(powerusage) as montlyPowerUsage
from usagetable 
where id = 1 and YEAR(date) = 2011 group by MONTH(date)

) t1

For Edited question

select AVG(montlyPowerUsage) from (

SELECT MONTH(date) as mnth,AVG(powerusage) as montlyPowerUsage
from usagetable 
where id = 1 and YEAR(date) = 2011 group by MONTH(date)

) t1

Upvotes: 3

ajreal
ajreal

Reputation: 47311

mysql> select avg(powerusage) 
from 
(select monthname(date), sum(powerusage) as powerusage 
from usagetable 
where id=1 and year(date)=2011
group by monthname(date)) as avg_usage;
+-----------------+
| avg(powerusage) |
+-----------------+
|       1700.0000 |
+-----------------+

select avg(total_powerusage) 
from 
(select monthname(date), sum(powerusage) as total_powerusage 
 from usagetable 
 where id=1 and year(date)=2011
 group by monthname(date)
) as avg_usage;

/* the use of subquery 
   is to return total of unique occurrences, 
   and sum powerusage of each occurrence,
   which mean, you just need to apply AVG into the subquery */

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

SELECT SUM(powerusage) / (MONTH(MAX(`date`)) - MONTH(MIN(`date`)) + 1)
           AS avgMontlyPowerUsage
FROM usagetable 
WHERE id = 1 
  AND YEAR(`date`) = 2011

or (depending on what you need when data is sparse):

SELECT SUM(powerusage) / COUNT( DISTINCT MONTH(`date`) )
           AS avgMontlyPowerUsage
FROM usagetable 
WHERE id = 1 
  AND YEAR(`date`) = 2011

Warning: Neither of the above is optimized for performance.

Upvotes: 0

Sean H Jenkins
Sean H Jenkins

Reputation: 1780

Try adding a group by on the id

GROUP BY id

Or the date, whichever suits.

Upvotes: 0

Related Questions