Board index » delphi » SQL Questions (STAT by Month)

SQL Questions (STAT by Month)

Hello!
I want to do some stat in a table by month. Does SQL statment exist for
such STAT or I have to do a temp table. I have paradox 7 Tables.

           Jan 1999        Feb 1998

item1    2000.00        3000.00
item2    1000.00        2000.00

Thanks!

 

Re:SQL Questions (STAT by Month)


Quote
On Tue, 24 Aug 1999 13:59:50 -0400, Joel Casse <bnc56...@abacom.com> wrote:
>I want to do some stat in a table by month. Does SQL statment exist for
>such STAT or I have to do a temp table. I have paradox 7 Tables.

>           Jan 1999        Feb 1998

>item1    2000.00        3000.00
>item2    1000.00        2000.00

Not in SQL -- at least not with a single SQL statement. What you are asking
for is commonly referred to as a cross-tab result set. The current SQL
standard includes no syntax to accommodate this, though I have seen this as
a proposal for SQL3.

You should be able to get this result using multiple local SQL statements.
The first statement would be a UNION join of multiple SELECT statements:
twelve statements, one for each of the twelve months in your desired result
set. In each of these SELECT queries, you would return the ItemNo field (or
whatever you named it), one nonzero value, and eleven zero values. The one
nonzero value is for the month the particular SELECT query represents: the
first query for January, the second for February, and so on. The nonzero
value appears in the column position of the SELECT query's intermediate
result set corresponding to the month. Every other month column contains a
zero. Filter the data in each of these twelve SELECT queries to only
include data for the corresponding month and year. Do this filtering in
each statement's WHERE clause, retrieving the month and year part of the
date using the EXTRACT function. If any aggregation is done in the SELECT
clause of the statements, also include a GROUP BY clause on the ItemNo
field.

In the example that follows, the data from the sample Paradox tables
Customer and Orders is retrieved and arranged into a cross-tab result set.
The Y-axis is based on the CustNo field and the X-axis on the twelve months
of 1988. The Orders.AmountPaid column is the aggregated data stored in this
matrix form.

The first query (saved to the filename FIRST.SQL):

/* Jan 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  SUM(O.AmountPaid) AS Jan, CAST(0.0 as NUMERIC) AS Feb, CAST(0.0 as
NUMERIC) AS Mar,
  CAST(0.0 as NUMERIC) AS Apr, CAST(0.0 as NUMERIC) AS May, CAST(0.0 as
NUMERIC) AS Jun,
  CAST(0.0 as NUMERIC) AS Jul, CAST(0.0 as NUMERIC) AS Aug, CAST(0.0 as
NUMERIC) AS Sep,
  CAST(0.0 as NUMERIC) AS Oct, CAST(0.0 as NUMERIC) AS Nov, CAST(0.0 as
NUMERIC) AS Dcr
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 1)
GROUP BY C.Company, C.CustNo
UNION ALL
/* Feb 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), SUM(O.AmountPaid), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 2)
GROUP BY C.Company, C.CustNo
UNION ALL
/* Mar 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), SUM(O.AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 3)
GROUP BY C.Company, C.CustNo
UNION ALL
/* Apr 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  SUM(O.AmountPaid), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 4)
GROUP BY C.Company, C.CustNo
UNION ALL
/* May 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), SUM(O.AmountPaid), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 5)
GROUP BY C.Company, C.CustNo
UNION ALL
/* Jun 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), SUM(O.AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 6)
GROUP BY C.Company, C.CustNo
UNION ALL
/* Jul 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  SUM(O.AmountPaid), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 7)
GROUP BY C.Company, C.CustNo
UNION ALL
/* Aug 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), SUM(O.AmountPaid), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 8)
GROUP BY C.Company, C.CustNo
UNION ALL
/* Sep 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), SUM(O.AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 9)
GROUP BY C.Company, C.CustNo
UNION ALL
/* Oct 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  SUM(O.AmountPaid), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 10)
GROUP BY C.Company, C.CustNo
UNION ALL
/* Oct 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), SUM(O.AmountPaid), CAST(0.0 as NUMERIC)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 11)
GROUP BY C.Company, C.CustNo
UNION ALL
/* Dec 1988 query */
SELECT C.CustNo, COUNT(AmountPaid),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC),
  CAST(0.0 as NUMERIC), CAST(0.0 as NUMERIC), SUM(O.AmountPaid)
FROM Customer C
  LEFT OUTER JOIN Orders O
    ON (C.CustNo = O.CustNo)
WHERE (EXTRACT(YEAR FROM O.SaleDate) = 1988) AND
  (EXTRACT(MONTH FROM O.SaleDate) = 12)
GROUP BY C.Company, C.CustNo
ORDER BY C.CustNo

This UNION query results in twelve (or up to twelve) records for each
distinct CustNo value.

The second query can be a saved query or SQL in a TQuery. This statement
uses a SELECT statement to produce the final result set in the format you
intended. This second query will be an aggregating statement, grouping on
the CustNo field. It uses the SQL function MAX on each of the twelve
columns that contain either a nonzero or a zero value (given one nonzero
and eleven zero values, the MAX function returns the nonzero value).

SELECT C.Company, C.CustNo, MAX(F.Jan) AS January, MAX(F.Feb) AS February,
MAX(F.Mar) AS March,
  MAX(F.Apr) AS April, MAX(F.May) AS May, MAX(F.Jun) AS Jun,
  MAX(F.Jul) AS July, MAX(F.Aug) AS August, MAX(F.Sep) AS September,
  MAX(F.Oct) AS October, MAX(F.Nov) AS November, MAX(F.Dcr) AS December
FROM Customer C
  LEFT OUTER JOIN "first.sql" F
    ON (C.CustNo = F.CustNo)
GROUP BY C.Company, C.CustNo
ORDER BY C.Company, C.CustNo

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

Re:SQL Questions (STAT by Month)


Thanks you Very much
I'm very happy

Joel Casse :)

Other Threads