Board index » delphi » Group by Month and Fill in Blank Months??

Group by Month and Fill in Blank Months??

I have data in a table that is something like this:

10              1/2/98
5               1/15/98
7               2/4/98
25              3/2/98
15              3/3/98
8               6/9/98
etc.......

and I am trying to get output in another table so I can print a report
and graph it that looks like this:

15              Jan98
7               Feb98
25              Mar98
0               Apr98
0               May98
8               Jun98

Can this be done in an SQL statement? Having trouble with the months
that are zero. Or should I just make a big table with all possible
months and years and fill in the total column where appropriate??
Thanks.

Terry Towle

 

Re:Group by Month and Fill in Blank Months??


On Thu, 19 Feb 1998 18:28:50 GMT, 102433.2...@compuserve.com (Terry W.

Quote
Towle) wrote:
>I have data in a table that is something like this:

>10          1/2/98
>5           1/15/98
>7           2/4/98
>25          3/2/98
>15          3/3/98
>8           6/9/98
>etc.......

>and I am trying to get output in another table so I can print a report
>and graph it that looks like this:

>15          Jan98
>7           Feb98
>25          Mar98
>0           Apr98
>0           May98
>8           Jun98

>Can this be done in an SQL statement? Having trouble with the months
>that are zero. Or should I just make a big table with all possible
>months and years and fill in the total column where appropriate??
>Thanks.

You can get the result you describe, but you will have to overcome some
hurdles to do so.

First, local SQL (what the BDE uses against Paradox and dBASE tables) does
not support row grouping based on derived values. That is, the GROUP BY
clause cannot contain a calculation (such as the result of the EXTRACT
function) or references to calculated values. To overcome this and group by
month, you would need to compose twelve distinct SELECT queries, one for
each month in the year. Each would filter based on the specific month for
the query and on the overall target year. You would then connect each of
these twelve queries into a single result set using the UNION join.

Second, SQL is predicated on using existing data, altering it if desired,
and then making that retrieved data available to an application. Making
nonexistent data available, such as filling in empty places in sequences
where data does not exist, requires special handling. In this particular
situation, though, it is easy and fit right in. You supply the month
designation as a string literal in each of the twelve queries described
previously. When an aggregating query (such as one using the SUM function)
does not have any rows on which to operate because of WHERE clause
criteria, the query returns a single row with "empty" data. In the case of
a SUM function, a value of zero is returned.

Here is an example of the sort of query you will need. The example below
assumes your two columns are named AMOUNT and TRANSDATE.

  SELECT SUM(amount), "Jan98"
  FROM YOURTABLE
  WHERE ((EXTRACT(MONTH FROM transdate) = 1) AND
   (EXTRACT(YEAR FROM transdate) = 1998))
  UNION
  SELECT SUM(amount), "Feb98"
  FROM YOURTABLE
  WHERE ((EXTRACT(MONTH FROM transdate) = 2) AND
   (EXTRACT(YEAR FROM transdate) = 1998))
  UNION
  ...
  SELECT SUM(amount), "Dec98"
  FROM YOURTABLE
  WHERE ((EXTRACT(MONTH FROM transdate) = 12) AND
   (EXTRACT(YEAR FROM transdate) = 1998))

To make the query more dynamic, you might use a parameter for the year
value in the twelve WHERE clause comparisons. The same parameter can be
used all twelve times.

**************************************************************************
Steve Koterski
Borland International, Inc.
http://www.borland.com/delphi
(Remove the "SPICEDHAM2" from the address. Death to spam-bots!)

Re:Group by Month and Fill in Blank Months??


On Fri, 20 Feb 1998 17:01:32 GMT, koter...@SPICEDHAM2borland.com

Quote
(Steve Koterski) wrote:

>You can get the result you describe, but you will have to overcome some
>hurdles to do so.

>First, local SQL (what the BDE uses against Paradox and dBASE tables) does
>not support row grouping based on derived values. That is, the GROUP BY
>clause cannot contain a calculation (such as the result of the EXTRACT
>function) or references to calculated values. To overcome this and group by
>month, you would need to compose twelve distinct SELECT queries,

Thanks, I see what you mean. I gave up on SQL and went back and put
together a table with all the months and years I could possibly
foresee and stepped through, filling in the correct slots. SLOW!! and
not elegant, but it works.

I appreciate your response and will start over.

Re:Group by Month and Fill in Blank Months??


Whoa there... Local SQl can support the Having Clause in the group by.

I assume that you probably have something that looks like this

Amount     Date
10            1/2/98

etc...

Where there can be multiple entries for each day and maybe no entries for
that day.
You can do this
SELECT Amount, createdDate FROM wherever ORDER BY Date

This would not even look at the value in amount

If you are using a query like this

SELECT sum(amount), createddate FROM wherever GROUP BY createddate
this to would work provided that you have a default value for the amount
field of ZERO,

You could take it even one step further
SELECT sum(amount), createddate FROM wherever GROUP BY createddate Having
sum(amount) > 1000

This would print out by createddate (in order) the months or days that had
amounts greater than 1000.

Granted there are some differences between Local Sql and say MS-SQL or
Oracle SQL what you are trying to do is not beyond the capabilities of the
BDE.

Good Luck
Chad Nale
cn...@storrite.com

Quote
Terry W. Towle wrote:
> I have data in a table that is something like this:

> 10              1/2/98
> 5               1/15/98
> 7               2/4/98
> 25              3/2/98
> 15              3/3/98
> 8               6/9/98
> etc.......

> and I am trying to get output in another table so I can print a report
> and graph it that looks like this:

> 15              Jan98
> 7               Feb98
> 25              Mar98
> 0               Apr98
> 0               May98
> 8               Jun98

> Can this be done in an SQL statement? Having trouble with the months
> that are zero. Or should I just make a big table with all possible
> months and years and fill in the total column where appropriate??
> Thanks.

> Terry Towle

Other Threads