Re: Extract use

Λιατίφης Κωσταντίνος wrote:
> Can you tell me how i use the Extract in a Date Field?
> I want to sum by a month. Please write an example

select EXTRACT(month from TRAN_DATE), sum(GRAND_TOTAL)
from TRAN_TABLE
where TRAN_DATE >= '1/1/2005'
group by EXTRACT(month from TRAN_DATE)

but this is bad, because it adds all Januaries together (unless, of 
course, this is what you want; you might want to see which month does 
better!). So, to make it like I think you want it, simply change it as 
follows:

select EXTRACT(year from TRAN_DATE), EXTRACT(month from TRAN_DATE), 
sum(GRAND_TOTAL)
from SALES_TRAN
where TRAN_DATE >= '1/1/2005'
group by EXTRACT(year from TRAN_DATE), EXTRACT(month from TRAN_DATE)

Hope this helps,

Dear Sir

Can you tell me how i use the Extract in a Date Field?
I want to sum by a month
Please write an example
Thank you for your time

"Zoren Lendry" <zorenlendry@yahoo.com> wrote in message 
news:4742788d$1@newsgroups.borland.com...
> Λιατίφης Κωσταντίνος wrote:
>> Can you tell me how i use the Extract in a Date Field?
>> I want to sum by a month. Please write an example
>
> select EXTRACT(month from TRAN_DATE), sum(GRAND_TOTAL)
> from TRAN_TABLE
> where TRAN_DATE >= '1/1/2005'
> group by EXTRACT(month from TRAN_DATE)
>
> but this is bad, because it adds all Januaries together (unless, of 
> course, this is what you want; you might want to see which month does 
> better!). So, to make it like I think you want it, simply change it as 
> follows:
>
> select EXTRACT(year from TRAN_DATE), EXTRACT(month from TRAN_DATE), 
> sum(GRAND_TOTAL)
> from SALES_TRAN
> where TRAN_DATE >= '1/1/2005'
> group by EXTRACT(year from TRAN_DATE), EXTRACT(month from TRAN_DATE)

Won't work in IB, unfortunately -- cannot GROUP BY anything other than a 
column (this is an interbase newsgroup, after all).

You could write 12 separate selects (one for each month) and combine them 
using UNION.

Dan 

dan palley wrote:
> Won't work in IB

Oops! Sorry. You could always create two computed fields, for reporting 
purposes. On rare occasions I have done that -- if the application has 
certain reports as a main requirement, it is sometimes useful to either 
de-normalize slightly, or store a bit of extra data, in order to make 
reporting both easier to write and faster to execute.