The report selects items from a table by date, and I want to summarise
the data by month. Is there an elegant way to do this with SQL or a
Quick Report function I have missed, or do I just have to go the long
way round?

If you got the view, you can make a group by month, like

select sum(ammount) from tableX group by _month...

NB: in MS-SQL you can directly make a group by Datepart(m,Datefield), so no
need of a view. But on other SQL servers like Interbase, this does not
work...

Thanks in advance

[...]

To summarize one month, using the sample Paradox table ORDERS.DB, in this
case for January, 1995:

  SELECT "01", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 1) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)

To summarize on a monthly basis for the same year, repeat the same SELECT
query once for each of the twelve months and connect them together into a
single result set with UNION clauses.

  SELECT "01", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 1) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "02", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 2) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "03", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 3) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "04", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 4) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "05", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 5) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "06", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 6) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "07", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 7) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "08", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 8) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "09", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 9) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "10", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 10) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "11", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 11) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)
  UNION
  SELECT "12", SUM(O."AMOUNTPAID")
  FROM "ORDERS.DB" O
  WHERE (EXTRACT(MONTH FROM O."SALEDATE") = 12) AND
    (EXTRACT(YEAR FROM O."SALEDATE") = 1995)

For instance, the SQL statement below using the sample Paradox table
ORDERS.DB...

  SELECT EXTRACT(MONTH FROM O.SaleDate) AS M, SUM(O.AmountPaid)
  FROM "Orders.db" O
  WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988)
  GROUP BY M

...results in the error "Invalid field name", referring to M as used in the
GROUP BY clause.

SELECT Extract(MONTH from FuelingDate) currmth, Extract(YEAR from
FuelingDate) curryr,
 ...
FROM ...
WHERE ...
ORDERBY FuelingDate

The SELECT statement containing the EXTRACT command is the important part of
the SQL.  Also, make sure you also order the data correctly using an ORDERBY.
 By extracting the month and year, you'll use these values in a TQRGroup
band.  When you add a TQRGroup, you'll automatically get a groupheader band.
You might have to do some manipulating to get this band into the correct
position.  I don't know how familiar you are with Quick Reports, but it tends
to order the bands the way it wants to, not the way you want to.  Since it
sounds like you simply wish to summarize the data, the Group Header band you
added will probably be blank.  You have to leave it on the report even if
it's empty.  The band you're really interested in is the GroupFooter band.
To add this, just add a TQRBand and set its type to Groupfooter.  Once you
get the groupheader in place above the detail band, the Groupfooter should
show up below the detail band. Without the GroupHeader band, the report won't
break correctly.  The TQRGroup bands break on any change in the TQRGroup's
Expression property.  You must use the TQRGroup component to get the
GroupHeader.  Quick Reports allows you to drop a TQRBand onto the report and
set its type to GroupHeader, but this does not allow you to group correctly.
If you want to group on month, use the following in your TQRGroup Expression:

STR(qryRMVMonthly.currmth) + STR(qryRMVMonthly.curryr)

Converting these values to a string first and then concatenating them is
important!  If you simply were to sum these two values, it wouldn't
work.  For instance Feb. 1994 would be 2 + 1994 = 1996  which would be
the same as Jan. 1995 (1 + 1995 = 1996).  Therefore, your grouping would
be off.  By concatenating the string, you get 21994 for Feb. 1994 and
11995 for Jan 1995.  Since these values are different, Quick Reports
will break the band correctly.  Unfortunately, Quick Reports doesn't
seem to directly support grouping on months.
Good Luck,
Charles Borromeo

PS there is a plethora of information on Quick Reports at the
borland.public.delphi.reporting-charting newsgroup.

In article <36B97F2F.1...@{*word*269}.net>,