Board index » delphi » Group by months from a date field with local sql

Group by months from a date field with local sql

Hi
I have a database with a date type field.
I want to run a query that will return the amount
of records per each month from the date field.
Can this be done with the local sql?
Thanks Sorin.
 

Re:Group by months from a date field with local sql


I know, for Oracle, you've got to do it like this:

select to_char(<date_field_name>, 'MM'), count(*)
from <table_name>
group by to_char(<date_field_name>, 'MM')

I don't know if it works with another db....

Quote
"sorin" <so...@netvision.net.il> wrote in message

news:90vn49$nse2@bornews.inprise.com...
Quote
> Hi
> I have a database with a date type field.
> I want to run a query that will return the amount
> of records per each month from the date field.
> Can this be done with the local sql?
> Thanks Sorin.

Re:Group by months from a date field with local sql


Quote
>I have a database with a date type field.
>I want to run a query that will return the amount
>of records per each month from the date field.
>Can this be done with the local sql?

It can not be done with a single query. It can be done with 2 queries.

First query
Select Extract(Month from D.DateFld) as MNTH

Then you need to use dbiMakePerminent to save the first query to a table so that
you can do a second query.   An alternative is to save the SQL to a text file
with a .qbe extension.  Then you can use that in another query

Select mnth , Count(mnth) as cnt from your.qbe

use dbimakePerment like this
DbiMakePermanent(query1.handle, 'temp.db',true);

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Group by months from a date field with local sql


The BDE can treat a text file as a view of its name ends in either .qbe or
.sql.

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:7of83tga6fdapi71jn2el8dlfe6a85rg2k@4ax.com...

Quote

> Then you need to use dbiMakePerminent to save the first query to a table
so that
> you can do a second query.   An alternative is to save the SQL to a text
file
> with a .qbe extension.  Then you can use that in another query

> Select mnth , Count(mnth) as cnt from your.qbe

Re:Group by months from a date field with local sql


Try something like:
select distinct year(date),month(date),count(*)
from x
group by 1,2
order by 1,2;

the third field returned will be the record count for each month by year and
month.

Quote
sorin <so...@netvision.net.il> wrote in message

news:90vn49$nse2@bornews.inprise.com...
Quote
> Hi
> I have a database with a date type field.
> I want to run a query that will return the amount
> of records per each month from the date field.
> Can this be done with the local sql?
> Thanks Sorin.

Other Threads