Board index » delphi » Summ by month - SQL

Summ by month - SQL

I have a table with two fields

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,

--
Natwar Lath
Lath Consultants
ROURKELA 769012 (INDIA)
~~~ Offshore Software Development ~~~
http://www.kalinga.com/lath

 

Re:Summ by month - SQL


The following is copied from the book DB2 Answers! by Richard Levich and
should work depending on the version of SQL server that you use- this book
is specifically for DB2 and ORACLE :

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.

Quote
Natwar Lath wrote:
> I have a table with two fields

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

> --
> Natwar Lath
> Lath Consultants
> ROURKELA 769012 (INDIA)
> ~~~ Offshore Software Development ~~~
> http://www.kalinga.com/lath

Re:Summ by month - SQL


Quote
On Tue, 4 Jan 2000 12:43:39 +0530, Natwar Lath <l...@kalinga.com> wrote:
>I have a table with two fields

>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

What database back-end are you using?

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx

Re:Summ by month - SQL


In article <387416aa.2078...@news.gte.net>, koter...@NOSPAMgte.net
says...

Quote
> On Tue, 4 Jan 2000 12:43:39 +0530, Natwar Lath <l...@kalinga.com> wrote:

> >I have a table with two fields

> >MyDate - tdatetime

Sorry, I use paradox local DB database.

--
Natwar Lath
Lath Consultants
ROURKELA 769012 (INDIA)
~~~ Offshore Software Development ~~~
http://www.kalinga.com/lath

Re:Summ by month - SQL


Quote
On Wed, 5 Jan 2000 09:46:58 +0530, Natwar Lath <l...@kalinga.com> wrote:
>In article <387416aa.2078...@news.gte.net>, koter...@NOSPAMgte.net
>says...
>> On Tue, 4 Jan 2000 12:43:39 +0530, Natwar Lath <l...@kalinga.com> wrote:

>> >I have a table with two fields

>> >MyDate - tdatetime

>Sorry, I use paradox local DB database.

Local SQL (the SQL implementation the BDE uses for Paradox, dBASE, and
FoxPro tables) does not support basing a GROUP BY clause on an expression.
An example of such an expression would be the extraction of the month and
year from a DATE or TIMESTAMP column using the EXTRACT function -- what you
would need to do. To get around this limitation, you need to use two
queries. The first acts as an SQL view would and only serves to extract the
month and year from the date. The second statement uses that intermediary
result set and further refines it by aggregating the data and accumulating
the aggregations in groups based on data values.

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.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx

Re:Summ by month - SQL


Thanks Steve for his detailed reply. The solution work very smoothly.

Thanks once again

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

Quote
> On Wed, 5 Jan 2000 09:46:58 +0530, Natwar Lath <l...@kalinga.com> wrote:

> >In article <387416aa.2078...@news.gte.net>, koter...@NOSPAMgte.net
> >says...
> >> On Tue, 4 Jan 2000 12:43:39 +0530, Natwar Lath <l...@kalinga.com> wrote:

--
Natwar Lath
Lath Consultants
ROURKELA 769012 (INDIA)
~~~ Offshore Software Development ~~~
http://www.kalinga.com/lath

Other Threads