Re: sum in SQL?

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

James

Use QM queries. Do you have the SQL development kit? Then STRQM and change
to the library where you want the query. Set STRQM to use SQL instead of
prompting (which is like Query/400).

Create a new query, say YTDTOTALS, and put your statement into the "source"
display. In the place of the date put a substitution variable, preceded by
an ampersand - so '2002-11-30' would be replaced by, say, &INVDATE

In the CL, use STRQMQRY yourlib/YTDTOTALS output(*print) setvar((INVDATE
'''' *cat &cldate *cat '''') in the CL. You can override the prtf
(QPQXPRTF) as you need.

Work with those apostrophes - you need the variable to result in a quoted
date in the SQL statement. There's info on it in the Query Management
programming manual, under database in InfoCenter.

If you want to get really good, create a QMFORM in STRQM, associated with
this query - it works kind of like Query/400 but has some more capabilities.

HTH

Vern

At 05:49 PM 12/10/2002 -0700, you wrote:
> On Tue, 10 Dec 2002, Peter Dow wrote:
>
> >     SELECT CUSTNBR, CUSTNAME, SUM(INVTOT), CUSTYTD
> >       FROM CUSTOMERFILE JOIN INVOICEFILE ON CUSTNBR = INVCUSTNBR
> >      WHERE INVMONTH = 12
> >      GROUP BY CUSTNBR, CUSTNAME, CUSTYTD
> >
> > I'm assuming that CUSTYTD is a field in your CUSTOMERFILE.
>
> Excellent!  Good assumption, the YTD totals are in the customer file.
> Here is what I used:
>
> select cmcst#6, cmcst#3, cmcsname, sum(ihinvtot), cmytdsal
> from custmp, invchp where
> cmcst#6=ihcst#6 and cmcst#3=ihcst#3 and ihinvdate>'2002-11-30'
> group by cmcst#6, cmcst#3, cmcsname, cmytdsal
>
> Now I wonder how I can pass a date to this from say a CL program?  And
> make it print instead of display?  I want to put this on a menu that
> prints out the results for the current month.
>
> James Rich