Pseudocode Enquiry: pls correct.

This is my DB schema...

P.Code   Qty.    Amt.
=====================
ABC       3       100
ABC       6         2
ABC       12       12
MNB        4       23
PLM        2       12



I need to do some sort like summary page to sum all the same p.code together to see the Qty and Amt.

This is my pseudocode,

-- select the data from DB order by P.COde (to make sure it's in sequence)

-- String ori_pcode = rs.getString("PCode");
   int counter = 1;

public Integer getQty() {
   if (Integer.parseInt(counter) == 1) {
        amt = add amt;
   } else {
    ..
...



????????

Actually I don;t know how to do....
wjh7554Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

allahabadCommented:
-- You can directly get this from table.
select sum(Amt) from DB group by Code;
-- group by will order for you also

 
0
allahabadCommented:
-- You can directly get this from table.
select sum(Amt) from DB group by Code;
-- group by will order for you also

 
0
wjh7554Author Commented:
Yes did that but got some problem...

SQL Statement : Select prdcode, prddesp, sum(qty) from DB group by prdcode, prddesp order by prdcode


But how am I going to retrieve the "SUM*QTY) from the statement?

Normal I will do this..

public String prddesp = "";
.
..
prddesp = rs.getSring("prddesp");
..
..

public String getPrddesp() {
   return prddesp;
}

But how can I retirieve the total of QTY ( sum(qty) ) ??


0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

wjh7554Author Commented:
allahabad,

seems that I need to do some sort like programming already.

SQL statement = "Select pCode, InvNum SUM(qty) as a, SUM(InvAmt) as b from DB group by pCode, InvNum order by pCode "
//No problems.

But my results is liket his

PCode   InvNum   InvAmt
=======================
DDU     DD8956    369
ABC     BA1001    442
ABC     PK8968    333
PKL     LMO233    100
PKL     LM0244    312

I need to make s sub-total at beside. How could I do that?
Still need programming right?? Can suggest the psedocode, in JSP or JavaBean?

PCode   InvNum    InvAmt    sub-total
=====================================
DDU     DD8956      369         369
ABC     BA1001      442
ABC     PK8968      333         776
PKL     LM0233      100
PKL     LM0244      312         412
               Grand Total :   1557




What do you think??
0
cheekycjCommented:
I am not sure how to do this via sql but in java just do this:

create an object called Product that has 3 properties:

class Product implements Serializable {
  String pCode;
  int qty = 0;
  int amt = 0;
 
  Product (String newPCode) { this.pCode = newPCode;}

  public String getPCode() { return this.pCode; }
  public void setPCode(String newPCode) { this.pCode = newPCode; }
  public int getQty() { return this.qty; }
  pulbic void setQty (int newQty) { this.qty = newQty; }
  public int getAmt() { return this.amt; }
  pulbic void setAmt (int newAmt) { this.amt = newAmt; }
}

// now you can add that to an arrayList

SQL statement = "Select pCode, SUM(qty) as qty_sum, SUM(amt) as amt_sum from table group by pCode order by pCode "

java.util.List products = new java.util.ArrayList();

while (rs.next()) {
  Product currentProduct = new Product(rs.getString("pcode"));
  currentProduct.setQty(rs.getInt("qty_sum");
  currentProduct.setAmt(rs.getInt("amt_sum");
  products.add(currentProduct);
}


Now you can iterate over products

java.util.Iterator prodIter = products.iterator();
while (prodIter.hasNext()) {
  Product currentProduct = (Product) prodIter.next();
  out.print(currentProduct.getPCode());
  out.print(currentProduct.getQty());
  out.print(currentProduct.getAmt());
}

HTH,
CJ
0
kennethxuCommented:
SQL statement = "Select pCode, InvNum, SUM(qty) as Qty, SUM(InvAmt) as Amt from DB group by pCode, InvNum order by pCode, InvNum"

int qtyTotal = 0, qtySubTotal = 0;
double amtTotal = 0, amtSubTotal = 0;
String lastPCode = "";

while( rs.next() ) {
  String pCode = rs.getString(1);
  String invNum = rs.getString(2);
  int qty = rs.getInt(3);
  double amt = rs.getDouble(4);
  if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
    System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
    qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
    qtySubTotal = 0; amtSubTotal = 0;
    lastPCode = pCode
  }
  System.out.println( pCode + " " + invNum + " " + qty + " " + amt );
  qtySubTotal += qty; amtSubTotal += amt;
}

System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
System.out.println( "Grand Total " + qtyTotal + " " + amtTotal );
0
kennethxuCommented:
sorry, should be:
if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {
0
wjh7554Author Commented:
Friend, how can I set my bigdecimal to zero ??
I mean the syntax, found out I can't do this.
sub_total = 0;
error;
unless i do this
sub_total= new BigDecimal("0.00");

is it the correct way to zero my sub_total?
0
kennethxuCommented:
>> is it the correct way to zero my sub_total?
yes, bigdecimal is immutable, you must always create a new object.
0
wjh7554Author Commented:
kennethxu, I think your logic got little bit wrong....
I change
if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
   System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
   qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
   qtySubTotal = 0; amtSubTotal = 0;
   lastPCode = pCode
 }

to
if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
   lastPCode = pCode;
   System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
   qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
   qtySubTotal = 0; amtSubTotal = 0;
 }


:-)

And... I am have way sovling another prblem. See either you are faster or me.

I want this,

PCode   InvNum    InvAmt    sub-total
=====================================
DDU     DD8956      369         369
ABC     BA1001      442
ABC     PK8968      333         776
PKL     LM0233      100
PKL     LM0234       22
PKL     LM0244      312         434
              Grand Total :   1557

BUt your code give me this,

PCode   InvNum    InvAmt    sub-total
=====================================
DDU     DD8956      369         369
ABC     BA1001      442         442
ABC     PK8968      333         776
PKL     LM0233      100         100
PKL     LM0234       22         122
PKL     LM0244      312         412
              Grand Total :   1557


And I am using JSP and JavaBean method... so quite some problem...

Planning to add in more code in the getSub_total method...

Is ithat the way??


0
kennethxuCommented:
>> kennethxu, I think your logic got little bit wrong....
>> if( ! lastPCode.equals( pCode ) || qtySubTotal != 0 ) {
that's not right, i have corrected in my comment right after the code, it should be:

if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {

my code is just to illustrate the idea, I have not time to design the beans and etc. you of cause can abstract code into beans.
0
kennethxuCommented:
just be sure:

if( ! lastPCode.equals( pCode ) && qtySubTotal != 0 ) {
  System.out.println( lastPCode + " Subtotal " + qtySubTotal + " " + amtSubTotal );
  qtyTotal += qtySubTotal; amtTotal += amtSubTotal;
  qtySubTotal = 0; amtSubTotal = 0;
  lastPCode = pCode
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cheekycjCommented:
did u try my code?

CJ
0
wjh7554Author Commented:
CJ,
I am so sorry....

I don't understand this fews things, but I knw it's good for me to learn. Arraylist and java.util.Iterator prodIter.

BUt I understand your logic and ideas.
:-)
0
wjh7554Author Commented:
TQ kennethux
0
kennethxuCommented:
my pleasure
0
cheekycjCommented:
No need to apologize, if you understand the logic.. that is good.  the APIs come with time :-)

CJ
0
wjh7554Author Commented:
CJ, go out sid to grab your pooints..

TQ.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.