Board index » delphi » Dates Group By Month

Dates Group By Month

I have a Dbase file with a date field.  I wish to group the dates by
month in a query.

pseudo-code example:

QUERY1.SQL.ADD('SELECT FORMATDATETIME('m',MYDAY) AS MONTH,');
QUERY1.SQL.ADD('PLANT_ID, SO2 FROM PLANTS ');
QUERY1.SQL.ADD('GROUP BY MONTH');

What is the proper syntax?

 

Re:Dates Group By Month


The syntax in local SQL is :

select extract(month from MYDATE) as mon, PLANT_ID, SO2 from PLANTS

however, if memory serves, you can't group on a dynamically created field in
local SQL.

--

Alain Quesnel

P.S.: remove the [brackets] from my address when replying by e-mail.

Quote
Henry Clarius <clarius.he...@epa.gov> wrote in message

news:3827FD3A.AA910E4D@epa.gov...
Quote
> I have a Dbase file with a date field.  I wish to group the dates by
> month in a query.

> pseudo-code example:

> QUERY1.SQL.ADD('SELECT FORMATDATETIME('m',MYDAY) AS MONTH,');
> QUERY1.SQL.ADD('PLANT_ID, SO2 FROM PLANTS ');
> QUERY1.SQL.ADD('GROUP BY MONTH');

> What is the proper syntax?

Re:Dates Group By Month


Quote
Henry Clarius wrote:

> I have a Dbase file with a date field.  I wish to group the dates by
> month in a query.

> pseudo-code example:

> QUERY1.SQL.ADD('SELECT FORMATDATETIME('m',MYDAY) AS MONTH,');
> QUERY1.SQL.ADD('PLANT_ID, SO2 FROM PLANTS ');
> QUERY1.SQL.ADD('GROUP BY MONTH');

> What is the proper syntax?

Have a look at the EXTRACT function in LocalSQL help.
_________
Andreas

Re:Dates Group By Month


On Tue, 09 Nov 1999 05:53:46 -0500, Henry Clarius

Quote
<clarius.he...@epa.gov> wrote:
>I have a Dbase file with a date field.  I wish to group the dates by
>month in a query.

>pseudo-code example:

>QUERY1.SQL.ADD('SELECT FORMATDATETIME('m',MYDAY) AS MONTH,');
>QUERY1.SQL.ADD('PLANT_ID, SO2 FROM PLANTS ');
>QUERY1.SQL.ADD('GROUP BY MONTH');

select Extract(MONTH from MYDAY) as MYMONTH, ...

Jan

Re:Dates Group By Month


You are right about groups and dynamically created fields (can't do).  Guess
I'll go down another avenue.
Thanks.
Quote
Alain Quesnel wrote:
> The syntax in local SQL is :

> select extract(month from MYDATE) as mon, PLANT_ID, SO2 from PLANTS

> however, if memory serves, you can't group on a dynamically created field in
> local SQL.

> --

> Alain Quesnel

> P.S.: remove the [brackets] from my address when replying by e-mail.

> Henry Clarius <clarius.he...@epa.gov> wrote in message
> news:3827FD3A.AA910E4D@epa.gov...
> > I have a Dbase file with a date field.  I wish to group the dates by
> > month in a query.

> > pseudo-code example:

> > QUERY1.SQL.ADD('SELECT FORMATDATETIME('m',MYDAY) AS MONTH,');
> > QUERY1.SQL.ADD('PLANT_ID, SO2 FROM PLANTS ');
> > QUERY1.SQL.ADD('GROUP BY MONTH');

> > What is the proper syntax?

Re:Dates Group By Month


On Wed, 10 Nov 1999 17:47:24 -0800, "Henry F. Clarius"

Quote
<hclar...@idsonline.com> wrote:
>You are right about groups and dynamically created fields (can't do).  Guess
>I'll go down another avenue.

You can do it in local SQL, it just takes two statements instead of one.
But first, I would ask why you are event trying to use the GROUP BY
function. Grouping is only valid when there is some aggregation going on
(i.e., functions like SUM, MIN, and MAX are used in the SELECT statement).
None of the columns in your example are aggregated. Perhaps you meant the
ORDER BY clause?

  SELECT EXTRACT(MONTH FROM MYDAY) AS MM, PLANT_ID, SO2
  FROM PLANTS
  ORDER BY 1

If you were really grouping... In the first statement you retrieve the data
to aggregate and the calculated value (the extraction of the month from the
date column). Save that statement to a text file (with the extension .SQL).

  SELECT EXTRACT(MONTH FROM MYDAY) AS MM, PLANT_ID
  FROM PLANTS
  ORDER BY 1, 2

Then, use that saved statement file in a second statement. Reference the
saved file where you would otherwise have specified a table. Do the
aggregation and grouping in this second statement.

  SELECT MM, COUNT(PLANT_ID)
  FROM "PLANTS.SQL"
  GROUP BY MM

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

Re:Dates Group By Month


I did wish to group by month. Thanks alot. Solution worked.
Quote
Steve Koterski wrote:
> On Wed, 10 Nov 1999 17:47:24 -0800, "Henry F. Clarius"
> <hclar...@idsonline.com> wrote:

> >You are right about groups and dynamically created fields (can't do).  Guess
> >I'll go down another avenue.

> You can do it in local SQL, it just takes two statements instead of one.
> But first, I would ask why you are event trying to use the GROUP BY
> function. Grouping is only valid when there is some aggregation going on
> (i.e., functions like SUM, MIN, and MAX are used in the SELECT statement).
> None of the columns in your example are aggregated. Perhaps you meant the
> ORDER BY clause?

>   SELECT EXTRACT(MONTH FROM MYDAY) AS MM, PLANT_ID, SO2
>   FROM PLANTS
>   ORDER BY 1

> If you were really grouping... In the first statement you retrieve the data
> to aggregate and the calculated value (the extraction of the month from the
> date column). Save that statement to a text file (with the extension .SQL).

>   SELECT EXTRACT(MONTH FROM MYDAY) AS MM, PLANT_ID
>   FROM PLANTS
>   ORDER BY 1, 2

> Then, use that saved statement file in a second statement. Reference the
> saved file where you would otherwise have specified a table. Do the
> aggregation and grouping in this second statement.

>   SELECT MM, COUNT(PLANT_ID)
>   FROM "PLANTS.SQL"
>   GROUP BY MM

> ==========================================================================
> Steve Koterski                  "Computers are useless. They can only give
> Technical Publications          you answers."
> Borland                                       -- Pablo Picasso (1881-1973)
> http://www.borland.com/techpubs/delphi

Re:Dates Group By Month


the problem in this case is that the user cant filter the first query.
so can we use TQuery instead of saved  "PLANTS.SQL"?

Quote
Steve Koterski wrote:
> On Wed, 10 Nov 1999 17:47:24 -0800, "Henry F. Clarius"
> <hclar...@idsonline.com> wrote:

> >You are right about groups and dynamically created fields (can't do).  Guess
> >I'll go down another avenue.

> You can do it in local SQL, it just takes two statements instead of one.
> But first, I would ask why you are event trying to use the GROUP BY
> function. Grouping is only valid when there is some aggregation going on
> (i.e., functions like SUM, MIN, and MAX are used in the SELECT statement).
> None of the columns in your example are aggregated. Perhaps you meant the
> ORDER BY clause?

>   SELECT EXTRACT(MONTH FROM MYDAY) AS MM, PLANT_ID, SO2
>   FROM PLANTS
>   ORDER BY 1

> If you were really grouping... In the first statement you retrieve the data
> to aggregate and the calculated value (the extraction of the month from the
> date column). Save that statement to a text file (with the extension .SQL).

>   SELECT EXTRACT(MONTH FROM MYDAY) AS MM, PLANT_ID
>   FROM PLANTS
>   ORDER BY 1, 2

> Then, use that saved statement file in a second statement. Reference the
> saved file where you would otherwise have specified a table. Do the
> aggregation and grouping in this second statement.

>   SELECT MM, COUNT(PLANT_ID)
>   FROM "PLANTS.SQL"
>   GROUP BY MM

> ==========================================================================
> Steve Koterski                  "Computers are useless. They can only give
> Technical Publications          you answers."
> Borland                                       -- Pablo Picasso (1881-1973)
> http://www.borland.com/techpubs/delphi

Other Threads