Little help for a query with average

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!!

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

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

Thanks from Italy!!

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...

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.

ZeldorBlat wrote:
> 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.


the CASE statement in general is extremely powerful. Those more
familiar with Oracle seem to like the decode, but I find it is confusing
unless you are the one that wrote it. Some database-engine-specific
syntax (ie NOT ANSI standard) is not very support friendly. The person
who wrote it may well understand it, but those who have to support it in
production spend an inordinate amount of time trying to figure out what
they did. Same thing goes for (+) (Oracle outer join syntax).