Board index » delphi » SQL Infromation needed in Paradox 7.0 for GROUP BY DATE conversion

SQL Infromation needed in Paradox 7.0 for GROUP BY DATE conversion

I use D4, Paradox 7.0  and use SQL Table. I need to use GROUP BY statement
using date field. But I want to group it by its day, month or year. How Can
I do it.

Example:

SELECT saledate, count(*) AS fcount
FROM "part.db"
GROUP BY EXTRACT(YEAR FROM saledate)

hakan

 

Re:SQL Infromation needed in Paradox 7.0 for GROUP BY DATE conversion


Quote
On Thu, 1 Oct 1998 11:01:00 +0300, "Ustad" <obj...@escortnet.com> wrote:
>I use D4, Paradox 7.0  and use SQL Table. I need to use GROUP BY statement
>using date field. But I want to group it by its day, month or year. How Can
>I do it.

>Example:

>SELECT saledate, count(*) AS fcount
>FROM "part.db"
>GROUP BY EXTRACT(YEAR FROM saledate)

Local SQL (that against Paradox and dBASE tables) does not support basing a
GROUP BY clause on such calculated values. It is still possible to
summarize data based on the month or year portion of a date field, though.
The following describes the process of doing this based on 12 months for a
single year using just SQL.

To do so, you would need to connect one monthly summary SELECT query for
each month (i.e., 12 SELECT queries) with UNION clauses. 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)

//////////////////////////////////////////////////////////////////////////
Steve Koterski                 "What is success in this world? I would say
Technical Publications         it consists of four simple things: to live
INPRISE Corporation            a lot, to love a lot, to laugh a lot, and
http://www.inprise.com/delphi  from it all, to learn a lot."
                                                     -- Richard J. Needham

Other Threads