How to caculate "SUM" in SQL (using WRKQRY)?

[uidamt98]

How to caculate "SUM" in SQL (using WRKQRY)?

In WRKQRY command using, I have problem do not now how to implement SQL with "SUM".
I have one PF file and need to caculate sum of quantity of each Item key (After that output to one PF file). Key field is Itemkey and User
Example:
Itemkey User Quantity
0000232 1000 500
0000232 1100 600
0000232 1200 850
0000484 5000 1000
0000484 5100 1100
---

I need the result is:
0000232 1950
0000484 2100


The interface of WRKQRY as my attachment.
I know to write this SQL by using command:
SELECT item, sum(quan)
FROM item
GROUP BY item;

This is the interface of WRKQRY:
OPT QUERY DEFINITION OPTION
> SPECIFY FILE SELECTIONS
> DEFINE RESULT FIELDS
> SELECT AND SEQUENCE FIELDS
> SELECT RECORDS
> SELECT SORT FIELDS
SELECT COLLATING SEQUENCE
SPECIFY REPORT COLUMN FORMATTING
> SELECT REPORT SUMMARY FUNCTIONS
> DEFINE REPORT BREAKS
> SELECT OUTPUT TYPE AND OUTPUT FORM
SPECIFY PROCESSING OPTIONS

But I must implement this by WRKQRY. In WRKQRY command, I do not know where can specify SUM operator and GROUP BY (I think we must place to put SUM or AVERAGE, OR COUNT !!!)

Please help me to study more about WRKSQL.

Thanks in advance.

[jamief]

Re: How to caculate "SUM" in SQL (using WRKQRY)?

May I ask if you can do this so easily in SQL why are you doing this with OPNQRYF or QUERY itself.

I know of no way to accomplish this with query...But then I am no expert in query.

Thanks
Jamie

[uidamt98]

Re: How to caculate "SUM" in SQL (using WRKQRY)?

Thanks for advice. I think the same to you. I search this teachnics in many sites on Google but not find how to solve this problem.
I think we can not implement complex SQL statements by using WRKQRY!
Therefore, I must implement by RPG code.

This is my code, and the result is ok:

************************************************** *************
C/EXEC SQL
C+ SET OPTION COMMIT = *NONE
C/END-EXEC
*
C/EXEC SQL
C+ INSERT INTO INVENPF
C+ SELECT I55303,I55304,I55305,SUM(I55310)
C+ FROM IC0553PF
C+ GROUP BY I55303,I55304,I55305,I55310
C/END-EXEC
C SETON LR
C RETRN
************************************************** *************

I try to use "OPNQRYF" but do not know what purpose of this command (I do not use this command yet :-))
Anyone have document or link about STRQRY please help me more for studying.:-)
Thanks you!

[arrow483]

Re: How to caculate "SUM" in SQL (using WRKQRY)?

In WRKQRY, you can do some SUM function. Look at "Select Report Summary Functions". It does NOT allow the details you can get in a SQL statement.

OPNQRYF allows you to select a result set and provide it as input to a RPG (or other language) program. Provides many of the functions of SQL SELECT statments. It usually used to provide sort/selection criteria outside the program.

See the IBM CL manual. A good book on it is "Open Query File Magic" by Ted Holt.

[newbie]

Re: How to caculate "SUM" in SQL (using WRKQRY)?

I ran into the same problem. My solution was to create a view with SQL then use that view within wrkqry.

[MikeS]

Re: How to caculate "SUM" in SQL (using WRKQRY)?

You can do this under WRKQRY.

First you sort your file by ITEMNUMBER.
On the report Summary Functions, select Total (1) for Quantity.

Then on Output type and Output Form select an Output type of Database file and
Form of Output to Summary.

This will output the required details to a file for you. (The last record in this file will be a grand total)

[Megha]

Re: How to caculate "SUM" in SQL (using WRKQRY)?

You can do this under WRKQRY as follows:-

1) Sort your file by ITEMNUMBER.
2) On the report Summary Functions, select Total (1) for Quantity.
3) On Define Report breaks, assign level 1 break to field Item Key
4) On Output type and Output Form select an Output type of Database file and Form of Output to Summary.

As a result you will get an output file with last two columns containing desired information i.e. ItemKey group and grand total of quantity of the ItemKey.

Hope this helps.

[gcraill]

Re: How to caculate "SUM" in SQL (using WRKQRY)?

Megha - check the previous posts it was from 2006, while your reply is accurate it may be a little late . . .

[kitvb1]

Re: How to caculate "SUM" in SQL (using WRKQRY)?

greg, u such a spoilsport!!
i found a couple from 2004 that haven't been answered and wanted to..... oh well, forget it

[rignhom]

Re: How to caculate "SUM" in SQL (using WRKQRY)?

Megha - check the previous posts it was from 2006, while your reply is accurate it may be a little late . . .

Even though the reply is late with regard to the OP but it helps me right now and I am sure will help the others too. Just want to say thanks to all you people.