MyDate - tdatetime
MyAmount - Currency

I would like to display the total of myamount fields on a monthly basis
such as :

04/1999 - 1000
05/1999 - 2000

I tried the same using SQL but could not do. I am sure there must be
someway of doing this by SQL and shall appreciate if any one can help me

Thanking you,

Select t2grp.COLx_Part, sum(T1.COLB)
from T1,
   ( select colz,
        month( Date_Column ) as COLX_PART
from t2 ) as t2grp
where t1.cola = t2grp.COLZ
group by t2grp.COLX_PART

This should actually group by the month within the date column. Now what is
needed is something like:

string(month(Date_Column)) + '/' + string(year(Date_Column)) instead of just
plain month(Date_Column).

Please note: that the function is pseudo SQL - it is not legal SQL syntax -
but it shows a way forward. It, as I noted before, depends on the SQL server
that you are using.

For the first query, do no aggregation or grouping. Use the EXTRACT
function to separate the month part of your DATE column.

  SELECT EXTRACT(YEAR FROM MyDate) AS yy,
    EXTRACT(MONTH FROM MyDate) AS mm,
    MyAmount
  FROM "TheTable.db"
  ORDER BY yy, mm

Save this statement to a text file (with a .SQL filename extension),
WITHMONTH.SQL for example. Saving it to a file allows it to be used as if
it were a table in the second statement.

In the second statement, use a SELECT statement to retrieve data through
the saved first query. In this statement, aggregate the data in the
MyAmount column using the SUM function. Group the aggregation using a GROUP
BY clause based on the MM column.

  SELECT yy, mm, CAST(yy AS CHAR(2)) || "/" || CAST(yy AS CHAR(4)) AS
    FullDate, SUM(MyAmount)
  FROM "WITHMONTH.SQL"
  GROUP BY yy, mm

If you do not desire the YY and MM columns to appear in visual controls
like the TDBGrid, hide them by setting their TField.Visible properties to
False after activating the TQuery foir that second statement.

Thanks once again

In article <38766e21.3616...@news.gte.net>, koter...@NOSPAMgte.net
says...