I am designing a Web application using InterDev and DreamWeaver that accesses an Oracle Table. I have an ASP page that runs a query of selected filters. The answers are then posted to a table on a results ASP page. I want to have report information that totals certain results of the query. for instance- Fields are INDEXPTS(numeric),MIR5P(varchar), STATUS(varchar), & TOTALCREDIT(numeric), ISSUETO(varchar).

If I query ISSUETO I may have several rows returned with various data from the other fields. It might look like this (under the column titles):

 
ISSUETO, MIR5P, INDEXPTS, STATUS, TOTALCREDIT
ABC CO, yes,  100,  closed,  234.90;
ABC CO, yes,  10,  closed,   45.00;
ABC CO,  ,   ,     open,  67.00;
ABC CO, no,   ,    open,  0.00 

In a separate table I want to know, specific to each query:
Number of rows returned (issueto), how many "yes" values were returned(mir5p), sum of index points(indexpts), how many "open" values were returned(status), and sum of charges(totalcredit) I got the sum of charges to work by: if not isnull(rs("totalcredit")) then total cost=totalcost + cdbl(rs("totalcredit")) and then the asp in the report table has <%=formatcurrency(totalcost)%> to display the results.

    Requires Free Membership to View

You may have to use the GROUP by function in your SQL statement. I set up a table called SHELLYS_TABLE (sorry I spelled your name wrong) and inserted data in the table as follows.

If you want an output that will be something like the following for the company ABC CO

SELECT issueto, MIR5P, status, SUM(indexpts), SUM(TOTALCREDIT) FROM SHELLYS_TABLE WHERE ISSUETO='ABC CO' GROUP BY ISSUETO, MIR5P, STATUS order by DECODE(MIR5P, NULL, '999999', MIR5P) DESC , STATUS DESC / If you want the counts of certain columns, say MIR5P and status then, you can use count function on these columns. For the second example I ran the query for the entire table, the query and its output is provided below:

SELECT issueto, MIR5P, status, count(MIR5P) CNT_M, count(status) CNT_S, SUM(indexpts) SUM_IDP, SUM(TOTALCREDIT), SUM_TC FROM SHELLYS_TABLE GROUP BY ISSUETO, MIR5P, STATUS order by ISSUETO, DECODE(MIR5P, NULL, '999999', MIR5P) DESC , STATUS DESC /

SELECT issueto, MIR5P, status, count(MIR5P), count(status), SUM(indexpts), SUM(TOTALCREDIT) FROM SHELLYS_TABLE WHERE ISSUETO='123 CO' GROUP BY ISSUETO, MIR5P, STATUS order by DECODE(MIR5P, NULL, '999999', MIR5P) DESC , STATUS DESC /

This was first published in February 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

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

Your password has been sent to: