On Apr 9, 5:29 pm, Michael Austin <maus...@firstdbasource.com> wrote:
> ZeldorBlat wrote:
> > On Apr 9, 8:41 am, "b.buz" <b....@email.it> wrote:
> >> Hello, I need a little help...
>
> >> I have a very simple table:
>
> >> date amount
> >> 2008-01-01 1000
> >> 2008-01-01 124
> >> 2008-01-02 333
> >> 2008-01-02 120
> >> ......
> >> 2009-07-06 1023
> >> 2009-07-07 144
> >> .....
>
> >> I need a simple query that gives me the average of amount for today,
> >> the current month, the previous month and the current year.
>
> >> A result like
>
> >> today_avg month_avg lastmonth_avg
> >> 1023 989 789
>
> >> can anyone help me?
>
> >> Thanks to everybody!!
>
> > Try this (untested):
>
> > select avg(case when date = curdate() then amount else null end)
> > today_avg,
> > avg(case when month(date) = month(curdate()) and year(date) =
> > year(curdate()) then amount else null end) month_avg,
> > avg(case when month(date) = month(date_sub(curdate(), interval
> > 1 month)) and year(date) = year(date_sub(curdate(), interval 1 month))
> > then amount else null end) lastmonth_avg,
> > avg(case when year(date) = year(curdate()) then amount else
> > null end) year_avg
> > from yourTable
>
> Just hope you didn't supply him with the answer to homework/schoolwork.
> I like to at least see some attempt on their part to try and resolve
> the issue. Not that they don't need help, but, should be at least some
> attempt to figure it out...

Point taken. Some people are better at disguising them than others :)

I do think the "case statement inside an aggregate" is a very powerful
tool that many people overlook, though.