Sum sql counts to a grand total

snteran · September 22nd, 2011, 12:38 PM

I have 3 sql counts and need to sum the 3 totals.

Code:

Set objRS2 = conn2.Execute("SELECT count(*) AS pTotal FROM server WHERE ITPOC = 'Projects'")
Set objRS3 = conn3.Execute("SELECT count(*) AS oTotal FROM server WHERE ITPOC = 'Ops'")
Set objRS4 = conn4.Execute("SELECT count(*) AS iTotal FROM server WHERE ITPOC = 'Infrastructure'")


gbTotals = ("pTotal" + "oTotal" + "iTotal")


response.Write "<tr>"
Response.Write "<td class=clsBodyText  align=center>" & "Projects" & "</td>"
Response.Write "<td class=clsBodyText align=center>" & objRS2.Fields("pTotal") & "</td>"
response.Write "</tr>"
response.Write "<tr>"
Response.Write "<td class=clsBodyText  align=center>" & "Ops" & "</td>"
Response.Write "<td class=clsBodyText align=center>" & objRS3.Fields("oTotal") & "</td>"
response.Write "</tr>"
response.Write "<tr>"
Response.Write "<td class=clsBodyText  align=center>" & "Infrastructure" & "</td>"
Response.Write "<td class=clsBodyText align=center>" & objRS4.Fields("iTotal") & "</td>"
response.Write "</tr>"
response.Write "<tr>"
Response.Write "<td class=clsBodyText  align=center>" & "Total" & "</td>"
Response.Write "<td class=clsBodyText align=center>" & gbTotals & "</td>"
response.Write "</tr>"

I'm new to asp coding and could use some help.

Thanks,

mat41 · September 22nd, 2011, 06:46 PM

try replacing this:

gbTotals = ("pTotal" + "oTotal" + "iTotal")

with:

gbTotals = (objRS2(0) + objRS3(0) + objRS4(0))

snteran · September 22nd, 2011, 06:57 PM

that worked!!!!!

Thanks

mat41 · September 22nd, 2011, 07:13 PM

Im sure ti did. of course you could also do this:

gbTotals = (objRS2("pTotal") + objRS3("oTotal") + objRS4("iTotal"))

One option uses the field name and one uses the record position in the query 0 being the first selected field.

An observation - you should only ever need one open connection for a DB no matter how many queries you have on a page. I notice you are using conn2, conn3 and conn4 - A bit unneccesary and very resource humgry

snteran · September 22nd, 2011, 07:21 PM

Thanks for the advice...I just got my "Beginning Active Server Pages" in the mail. Let the learning begin!

mat41 · September 22nd, 2011, 08:02 PM

welcome to the Wrox community and the ASP world.....Most would say why would you learn classic ASP and not learn ASP.Net.

Our office still has alot of demand for Classic ASP however you are aware its no longer supported and many say its a dying language?

All that said there are many many many classic ASP sites out there in the world, I am still very busy with it..........but if you are young and strive to be a very employable programmer you should seriously think about Object Oriented programming in the .Net world

snteran · September 22nd, 2011, 11:54 PM

Hey Matt,

I eventually want to learn C#, however our company has an intranet site that was built using Classic ASP. Before I arrived, no one even attempted to make changes to the site, we have so many request for improvements but no one who wants to try to help develop the site since it's an old technology and to migrate it over would take a lot of time and money. I use to create PHP sites and figured I would get back to some web development, this will be a great starting point. I think once I have a good handle on the ASP pages, I'll start setting a course to migrate the site to .Net. I have taken some Java courses and plan to go back to school to get a good understanding on OOP.
Again, Thanks for your help and I'm sure we will be in touch via the site.

btw - I'm not that young but I'm eager to learn and build new skill sets all the time. I'm primarily a Sys Admin who sees the future moving more towards the cloud and less on managing Active Directory. I think pretty soon all us Sys Admins will be looking for other gigs if we don't start learning new technology.

Take care