Board index » delphi » SQL Statement, How Can I group records monthly ?

SQL Statement, How Can I group records monthly ?

1. How Can I group records monthly ? or even daily and yearly ?

2. I have a table TRX and want to create 2 queries , say monthly and yearly.
   Is it possible to do like this :
   I create a monthly query first and then build a yearly query based on the
first query.
   If possible, how can I do this ?
   I thank it will be faster then I create two queries based on the table if
there is a huge of data.
  Thanks.

 

Re:SQL Statement, How Can I group records monthly ?


Quote
On Mon, 24 May 1999 20:28:10 +0800, "Leo Chow" <l...@mcmaster.com.hk> wrote:
>1. How Can I group records monthly ? or even daily and yearly ?

>2. I have a table TRX and want to create 2 queries , say monthly and yearly.
>   Is it possible to do like this :
>   I create a monthly query first and then build a yearly query based on the
>first query.
>   If possible, how can I do this ?
>   I thank it will be faster then I create two queries based on the table if
>there is a huge of data.

Local SQL (what the BDE uses for dBASE, Paradox, and FoxPro databases) does
not support basing a GROUP BY clause on derived values. An example of a
derived value is the value returned by the SQL function EXTRACT, which
would be necessary to to the subtotalling on a monthly or yearly basis as
you describe. However, it is possible to do this using two SQL statements.
The key is to save the first statement to a .SQL file so that it acts as an
equivalent to a VIEW. The following description of this process uses the
sample Paradox table ORDERS.DB.

The first SQL statement should perform no aggregation (such as totalling
with the SUM function). It should return the column to be totalled, the
month (or year) from the DATE column for the grouping, and any other
columns to be used in the grouping. The following is the preliminary query
for a month-based aggregation for the year 1988.

  SELECT EXTRACT(MONTH FROM SaleDate) AS MM, AmountPaid
  FROM "Orders.db"
  WHERE (EXTRACT(MONTH FROM SaleDate) = 1998)

Save this statement to a .SQL file (a simple text format file containing
the SQL statement). For this example, assume a saved file name of
PRELIM.SQL.

The second statement will use the saved .SQL file instead of the base
table. By doing this, MM is seen as a column rather than as a derived value
and so can be used in the second query for the totals grouping. In this
second query, perform the aggregate operations and group on non-aggrgated
columns.

  SELECT MM, SUM(AmountPaid)
  FROM "Prelim.sql"
  GROUP BY MM

The same thing can be done to total rows with subtotals based on the year
portions of the DATE column. Simply use the YEAR keyword with the EXTRACT
function. Plus, the first query need not filter in the WHERE clause to a
single year.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Other Threads