Home > IT Answers > Database > Embedded SQL using SUM or some other method to total up qty and display 1 unique record
Dobie
80 pts.
 Embedded SQL using SUM or some other method to total up qty and display 1 unique record
Embedded SQL, SQL, SQL Select, SQL Select statement problem, SQLRPG
I have an sqlrpgle pgm setup, I want to add another sql select statement. I have a file that contains barcode label data, where multiple records can be assigned to the same Master serial #. My sqlrpg pgm is display various sorts in a screen inquiry. I want to setup my select, to display one record for each MASTER serial #(acsr2), and display how much total qty (add up SHQT for each ACSR2 field)  is assigned to each master serial #.  I am not sure of the syntax to use in embedded sql to do this.  I need help on the correct syntax to use to build the SQL Select statement to do this function. 

Sample of Data:

  [

CUCD SCSR2      ACSR2      SHQT     SHDT      AM         51        166        1  20110509  AM         52        166        1  20110509  AM         53        165        1  20110510  AM         54        165        1  20110510  AM         55        165        1  20110510  AM         56        170        1  20110518  AM         57        170        1  20110518  AM         58        170        1  20110519  AM         59                   1  20110519  AM         60        170        1  20110519  AM         61                   1  20110520 

 p]AM         61                     1              20110520 

 

 Example of one of my built select statements:


0113.01                                                                   0113.02  * loaded racks    not shipped .............                      0113.04 D Sel3            s            100a   inz('WHERE cucd = ''AM'' -  0113.05 d                                     and shnm = ''     '' -      0113.06 d                                     ORDER BY acsr2')            0113.07                                                                   0113.08                                                                  

                                     



So what I want displayed is:



MASTER SER#              ASM COUNT          SHIP DATE

165                                     3               5/10/2011

 







Software/Hardware used:
AS400 v5r4
ASKED: June 9, 2011  11:30 AM
UPDATED: June 10, 2011  10:02 PM
RELATED QUESTIONS
Answer Wiki:
select ser#, <b>count(*) </b> as count, <b>sum(qty)</b> as total from file <b>group by </b>ser#
Last Wiki Answer Submitted:  June 9, 2011  1:44 pm  by  BigKat   7,185 pts.
All Answer Wiki Contributors:  BigKat   7,185 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Here is the Sample Data again:
CUCD SCSR2 ACSR2 SHDT SHQT
AM 49 166 20110509 1
AM 50 165 20110510 1
AM 51 165 20110510 1
AM 52 165 20110510 1

Here is the sample of the partial select statement:
0113.02  * loaded racks    not shipped .............                      
0113.04 D Sel3            s            100a   inz('WHERE cucd = ''AM'' -  
0113.05 d                                     and shnm = ''     '' -      
0113.06 d                                     ORDER BY acsr2')            
0113.07                                                                   

This is what I want displayed:
MASTER SER#                               ASM COUNT                           SHIP DATE
165                                                          3                                          20110510
Dobie
 80 pts.
 

ok , Ive got this much to work, but I need to select ALL fields in the file. Or at least a select 8 fields that I need. When I insert my * for ALL, I get an error, when I try to select the fields I need thru STRQRY, I stil get an error. 

                                                           
SELECT acsr2, count(*) as count, sum(shqt) as total FROM   
MERTAS/SGBCSHPF GROUP BY acsr2
Dobie
 80 pts.
 

I stil get an error.

Error? What error? What is the message identifier, or at least the text?

Tom

TomLiotta
 108,330 pts.
 

Every field that you include in the select will need to be in the Group By clause except the count and sum.

You will get a count and total for each distinct set of the 8 fields.

I don’t think that’s what you want but that’s what you asked for.

Since you will be getting back multiple rows, the embedded code will need a cursor.
Phil

philpl1jb
 44,220 pts.
 

When I insert my * for ALL…

When I first read that, I thought it referred to the asterisk in “count(*)”.

So, the statement in the comment isn’t the one that throws the error?

Most likely, Phil’s comment is getting close to the problem. Show us the statement that actually throws an error (and show us the error). It ought to be possible to describe in terms of the statement why the error appears. That should clarify how to avoid it in the future.

Tom

TomLiotta
 108,330 pts.