?
Solved

MySQL and JSP: combining and saperating queries

Posted on 2003-11-05
5
Medium Priority
?
229 Views
Last Modified: 2010-04-01
i have 2 tables same structure but different data.
i have html table generated dynamically using data from the above tables.

the syntax:
<table width="100%" border="1">
        <tr bgcolor="#3399FF">
          <td colspan="5"><div align="center"><font color="#FFFFFF">
<strong>Out</strong></font></div></td>
        </tr>
        <tr>
          <td width="20%"><div align="center">Date</div></td>
          <td width="15%"><div align="center">TT No</div></td>
          <td width="15%"><div align="center">From</div></td>
          <td width="35%"><div align="center">Desc</div></td>
          <td width="15%"><div align="center">Amount</div></td>
        </tr>
<%
      Statement stmt4  = C.createStatement();
      ResultSet RS4    = stmt4.executeQuery("select *,sum(amount) as total from papertrans where type='out' group by type");
      
      while(RS4.next()){
            pdate2 = RS4.getString("date");
            pttno2 = RS4.getString("ttno");
            pfrom2 = RS4.getString("loc");
            pdescription2 = RS4.getString("description");
            pamount2 = RS4.getFloat("amount");
            float ptotal2 = RS4.getFloat("total");
%>
        <tr>
          <td width="20%"><div align="right"><%=pdate2%></div></td>
          <td width="15%"><div align="right"><%=pttno2%></div></td>
          <td width="15%"><div align="right"><%=pfrom2%></div></td>
          <td width="35%"><div align="right"><%=pdescription2%></div></td>
          <td width="15%"><div align="right"><%=sharePrice.format(pamount2)%></div></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>Total</td>
          <td><div align="right"><%=sharePrice.format(ptotal2)%></div></td>
        </tr>
     </table>

question:
it only managed to show the 1st info but it showed the amount(ptotal2) correctly.
how am i suppose to display the other info without saperating the query into 2?
0
Comment
Question by:yapsing
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
savalou earned 100 total points
ID: 9691710
Don't calculate the total in your query and don't group by type.  So you get all your records out.  Calculate your total in your JSP.

          float ptotal2 = 0;
     ResultSet RS4    = stmt4.executeQuery("select *,sum(amount) as total from papertrans where type='out'");
   
     while(RS4.next()){
...
         ptotal2 += pamount2;

0
 
LVL 19

Assisted Solution

by:Kuldeepchaturvedi
Kuldeepchaturvedi earned 100 total points
ID: 9691939
change is like..
<%
    Statement stmt4  = C.createStatement();
    ResultSet RS4    = stmt4.executeQuery("select * from papertrans where type='out' group by type");
   
     while(RS4.next()){
          pdate2 = RS4.getString("date");
         pttno2 = RS4.getString("ttno");
         pfrom2 = RS4.getString("loc");
         pdescription2 = RS4.getString("description");
         pamount2 = RS4.getFloat("amount");
         float ptotal2 += pamount2;
%>
that should do it









0
 
LVL 3

Expert Comment

by:savalou
ID: 9692321
yeah, I'd say it should be
     ResultSet RS4    = stmt4.executeQuery("select * from papertrans where type='out'");
Still no need to group by.
0
 
LVL 1

Author Comment

by:yapsing
ID: 9692394
another problem:
i only need to show the total once.

 <td width="15%"><div align="right"><%=sharePrice.format(pamount2)%> </div></td>
   </tr>
<%
   }//i can't put this here
%>
    <tr>
    <td>&nbsp;</td>
...
    <td><%=sharePrice.format(ptotal2)%></td>
0
 
LVL 1

Author Comment

by:yapsing
ID: 9692557
okie, i think i got it
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We are witnesses that everyone is saying that our children shouldn't "play" with a technology because it is dangerous. This article is going to prove that they are wrong.
The top devops trends for 2017 are focused on improved deployment frequency, decreased lead time for change and decreased MTTR.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month9 days, 13 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question