Embedded SQL using SUM or some other method to total up qty and display 1 unique record

80 pts.
Embedded SQL
SQL Select
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

select ser#, <b>count(*) </b> as count, <b>sum(qty)</b> as total
from file
<b>group by </b>ser#

Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Dobie
    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')            
    This is what I want displayed:
    MASTER SER#                               ASM COUNT                           SHIP DATE
    165                                                          3                                          20110510
    80 pointsBadges:
  • Dobie
    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   
    80 pointsBadges:
  • TomLiotta
    I stil get an error. Error? What error? What is the message identifier, or at least the text? Tom
    125,585 pointsBadges:
  • philpl1jb
    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
    54,090 pointsBadges:
  • TomLiotta
    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
    125,585 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: