CODEWITHSUNDEEP
mysqlsubquerysumaverage
Joe Casini
Joe Casini

Reputation: 141

SQL AVG and SUM GROUP BY

i have two tables, say Costs and Payments, and i need to calculate the AVG of monthly payments for each costs.

i.e. Costs_IdCost = 2 has three payments

+-----------+--------------+----------+--------+
| IdPayment | Costs_IdCost |   Date   | Amount |
+-----------+--------------+----------+--------+
|     1     |      2       |2012/09/10|  1000  |
+-----------+--------------+----------+--------+
|     2     |      2       |2012/09/20|  3000  |
+-----------+--------------+----------+--------+
|     3     |      2       |2012/10/01|  5000  |
+-----------+--------------+----------+--------+

now i need not just the average of payments (3000) but the average between:

September : (1000+3000)/2 = 2000
October   : 5000 /1 = 5000
AVG       : (2000+5000)/2 = 3500

and i'm getting pretty messy with group by and subquery >_<

thank you!

--------------------EDIT---------------------------

i'm using mySql, this is what i did so far:

SELECT c.IdCost,
c.Name,
c.Amount,
AVG(p.monthly_sum) Mean,
SUM( p.Amount ) Total,
COUNT( p.IdPayment ) Num
FROM Costs c
LEFT JOIN (SELECT MONTH(Date), 
        YEAR(Date), 
                    Costs_IdCost,
                    IdPayment,
                    Amount,
        AVG (Amount) monthly_sum
    FROM Payments
    GROUP BY YEAR(Date), MONTH(Date) 
     ) p ON p.Costs_IdCost = c.IdCost
GROUP BY c.IdCost

Upvotes: 0

Views: 836

Answers (3)

Jos America
Jos America

Reputation: 1

I do similar by using a select entry of 

SELECT format(DATE_TIME,''yyyy-MM'') as [Month], .......

GROUP BY format(DATE_TIME,''yyyy-MM''), .........

The SORT BY is the same as what GROUP BY I am using. Not that one can even got to the hour, minute and second level with this concept if you want example: SELECT format(DATE_TIME,''yyyy-MM-dd.HH'') as [Hour]

Upvotes: 0

Taryn
Taryn

Reputation: 247650

I am not sure if this is exactly what you are looking for but it will give you the desired results:

select date_format(date, '%Y-%m') Month,
  avg(amount) AvgAmount
from costs c
left join payments p
  on c.id = p.costs_idcost
group by date_format(date, '%Y-%m')
union all
select concat(cast(count(*) as char), ' Months Averaged'),
  avg(AvgAmount) TotalAvg
from
(
  select avg(amount) AvgAmount, 
    date_format(date, '%Y-%m') Month
  from costs c
  left join payments p
    on c.id = p.costs_idcost
  group by date_format(date, '%Y-%m')
) x

See SQL Fiddle with Demo

Upvotes: 2

Turcia
Turcia

Reputation: 711

You can group by month part of date.

GROUP BY datepart(MONTH, Cost.Date)

This may not the most efficient way, but it might work nicely. BTW datepart is build in function of MSSQL.

Upvotes: 1

Related Questions