how can i display the totals only

squarefish · November 4th, 2004, 06:40 AM

I have some code I have written to list the total number of PC's in each department of each location of our business. So you should see the following output.

Location 1
              sales 4
              parts 5
              other 9

Location 2
              accounts 14

However I have got so far but can not get to the totals only, my page comes out as follows

Location 1 sales 1
              sales 2
              sales 3
              sales 4
              parts 1
              parts 2

etc

Can anyone tell me how to display totals only, my code is below

Thanks

Richard

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rsCitrix
Dim rsCitrix_numRows

Set rsCitrix = Server.CreateObject("ADODB.Recordset")
rsCitrix.ActiveConnection = MM_lee_STRING
rsCitrix.Source = "SELECT * FROM tblCitrix ORDER BY location, department"
rsCitrix.CursorType = 0
rsCitrix.CursorLocation = 2
rsCitrix.LockType = 2
rsCitrix.Open()

rsCitrix_numRows = 0
%>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<div align="center">
  <p><strong>PC's by Location and Department</strong></p>
</div>
<table width="80%" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td>nbsp;</td>
    <td>nbsp;</td>
    <td width="33%"><div align="center">Total PC's</div></td>
  </tr>
      <%
    currentlocation = ""
    currentdept = ""
    nextlocation = rsCitrix("location")
    nextdept = rsCitrix("department")
    mycount = 1
    DO WHILE NOT rsCitrix.EOF = TRUE
        if NOT nextlocation = currentlocation then
            currentlocation = nextlocation
            %>
            <tr>
                <td colspan="3"><%=currentlocation%></td>
            </tr>
                <%
                do while (NOT rsCitrix.EOF = true)
                    if not currentlocation = rsCitrix("location") then
                        currentdept = nextdept
                        nextdept = ""
                        mycount = 1
                        exit do
                    end if

                    currentdept = nextdept
                    rsCitrix.movenext
                    if not rsCitrix.eof = true then
                        nextdept = rsCitrix("department")
                    else
                        exit do
                    end if
                    if currentdept = nextdept then
                        mycount = mycount + 1
                    else
                        mycount=1
                    end if

                    %>
                    <tr>
                        <td width=33%> </TD>
                        <td width=33%><%=rsCitrix("department")%></td>
                        <td width=33%><%=mycount%></td>
                    </tr>
                    <%

                LOOP
        end if
        if not rsCitrix.eof = true then
            'rsCitrix.movenext()
            nextlocation = rsCitrix("location")
        end if
    LOOP
    %>
</table>

</body>
</html>
<%
rsCitrix.Close()
Set rsCitrix = Nothing
%>

gokul_blr · November 4th, 2004, 07:57 AM

Can you have a different query result with Sum(xxx) group by etc., and try to display result at the end of the details.

Gokulan Ethiraj

happygv · November 4th, 2004, 06:11 PM

Hi Richard,

I can understand that you are trying to list the details for every location. But can you name the header for the data displayed within each location? What is the column used in the table?

Location 1
Hope this is department what is this, is that the count of department for that location?
              sales 4
              parts 5
              other 9

Gokul,

Using another query that has sum(xxx) wouldn't help, as Richard is looking for the sub total, which has to be displayed at the end of every location(If I am right). Also the method that you suggested would hit the DB for every location, thus would have make as many connections as number of locations to the DB.

Cheers!

_________________________
- Vijay G
Strive for Perfection

squarefish · November 5th, 2004, 05:59 AM

each record in the database is for 1 computer so I am trying to list number of PC's per department, per franchise

location department number of PC's
location1 sales 5
service 4

location2 sales 8
admin 2

Thanks Vijay

happygv · November 9th, 2004, 07:23 AM

Hi,

Hope this is what you are looking for.

Code:

select location, department, count(*) as NumOfPCs 
from tblCitrix 
group by location, department 
order by location, department

This would create list of locations, its relavant department and count of records per department, which you can loop through in your ASP page and display the location only when it changes, so that you get the Location_NAME displayed per location and display the other columns as such.

Feel free to post here for any clarifications.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection