Must be an easier way to count records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Must be an easier way to count records

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2011-09-30 : 03:38:06

Hello.

I've got about a year of SQL programming experience and a few years of ASP as well. I've been able to create an entirely SQL driven website that allow people to upload photos, search for photos, etc.

My knowledge of SQL is limited though (select,update,delete,order by, etc.)

I have a main page on my site with the names of over 200 cities. Each city tells you the number of locations in that city for you to visit.

Toronto, Ontario 2 locations
New York, NY 8 locations
San Jose, CA 12 locations

This list is generated by a SQL table named "sites" containing hundreds of locations (over 2000). The table lists locations and the cities they are found in.

name // city
CN Tower // Toronto
Eaton Centre // Toronto
The Falls // Niagra Falls
someplace // New York

I read through the table named "sites" with this command:
SELECT * from sites ORDER by city

to go through all 2000+ locations ordered by the city they are located in. In order to come up with a number of locatios per city I use a very sloppy and probably unnecessary method to count them.

Hopefully my concern will become clearer when I show you my code:

'open all records from the table "sites" which is my cities
'put them in order by the city they are in
'example cities: Aaron, Brantford, Chatham, Fielding

strSQL = "SELECT * FROM sites ORDER by city"
set objrs = objconn.Execute(strSQL)

' no records exist? show error message
if (objRS.BOF and objRS.EOF) then
error message goes here...
end if

objrs.Movefirst 'first record

' MAIN LOOP
while NOT objrs.EOF
cstring = objrs("city") ' this is the city
count = 0 ' number of locations per city is zero

'the cycle (this is probably CPU comsuming)
do until objrs("city")) <> cstring
count = count + 1
objrs.movenext
Loop

' So assuming that the first city is named Aaron then cstring
' equals "Aaron" and the Do/Until will loop, incrementing the
' value of count until such time that the next recordset is NOT
' EQUAL to "Aaron" meaning it is time to begin a new count for
' the next city in the list. Sloppy huh?

' capitalize the city name
cstring = UCase(Left(cstring,1)) & Mid(cstring,2)
' put it into master table
strsql2 = "INSERT INTO master (city,noloc,updflag,newflag) VALUES ('" & cstring & "','" & count & "','" & updflagi & "','" & newflagi & "')"
set objRS2 = objconn.Execute(strSQL2)

In essence, I cannot use the above method to cycle through 200 cities containing 2000 locations total using a Do while loop. So I put the list of cities and the number of locations in each into a NEW table named Master.

The index.asp page of my site reads the Master table. It is not real time, as I have to manually run the above script to generate my main table.

In a perfect setting, someone on here could tell me how to do this all from the actual table of cities.

I am aware of the SUM command but I am not looking to count the sum of records for JUST ONE city, but for EVERY city. I want to read through 2000 records and do the following:
- identify unique city names
- come up with a count of all records that match the unique city names

Any help would be greatly appreciated :)

The actual table can be seen here
http://i51.tinypic.com/2zqr8cy.jpg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 03:56:29

just make your query as below


SELECT city,COUNT(*) AS CityCnt from sites 
GROUP BY city
ORDER by city

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-09-30 : 04:26:32

You can count total sites in a city using group by clause of sql and use count(sites) to get total count.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

oap
Yak Posting Veteran

60 Posts

Posted - 2011-09-30 : 05:33:43

Thank you for the quick replies. I'm not sure though how to use this to display the fields.

I need to display each of the cities followed by the # of locations found for each.

How do I retrieve the value of citycnt and then move onto the next record?

strSQL = "SELECT city,COUNT(*) AS CityCnt from sites GROUP BY city ORDER by city"
set objrs = objconn.Execute(strSQL)

while not objrs.EOF
response.write "<br>" & objrs("city") & "..." & objrs.Fields("CityCnt").value
objrs.movenext

This doesn't work... I don't know how to retrieve all of the records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 05:35:09

why you need to move row by row. the query i gave gives all the city names as well as count of each against it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2011-09-30 : 05:44:54

Sorry, I'm really still wet behind the ears when it comes to SQL.

while not objrs.EOF
response.write objrs("closest") & objrs("citycnt")
objrs.movenext
wend

worked perfectly! It saves a lot of time, literally and on the server.

Thank you so much guys. :)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-30 : 05:47:33

visakh: this is asp code to iterate through the recordset. It doesn't run the query multiple times.

oap: I don't know why you added the .Fields and .value to the CityCnt but if you remove it, it should work:


trSQL = "SELECT city, COUNT(*) AS CityCnt from sites GROUP BY city ORDER by city"
set objrs = objconn.Execute(strSQL)

while not objrs.EOF
  response.write "<br>" & objrs("city") & "..." & objrs("CityCnt")
objrs.movenext

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 05:49:30

quote:
Originally posted by oap

Sorry, I'm really still wet behind the ears when it comes to SQL.

while not objrs.EOF
response.write objrs("closest") & objrs("citycnt")
objrs.movenext
wend

worked perfectly! It saves a lot of time, literally and on the server.

Thank you so much guys. :)

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2011-09-30 : 06:07:12

If I may bother you for one more thing...

I have it now displaying all the cities and the number of locations in each.

However in the table I also have a column titled "new" which contains the status of each city ('y' or 'n' for whether new pics are present)

I tried this:
strSQL = "SELECT new,closest,COUNT(*) AS CityCnt from sites GROUP BY closest ORDER by closest"

but I get this error:
Column 'sites.new' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'd like to add the 'new' column to the results too.

:D

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-30 : 06:21:31

SELECT new,closest,COUNT(*) AS CityCnt from sites GROUP BY new, closest ORDER by closest

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

cmbergin
Starting Member

14 Posts

Posted - 2011-09-30 : 10:10:38

Not quite, Lumbago. That will return a "yes" row and a "no" row for every city with new sites.

SELECT closest, COUNT(*) AS CityCnt, SUM(CASE new WHEN 'y' THEN 1 ELSE 0 END) AS NewCount
FROM sites
GROUP BY closest
ORDER BY closest

In the application, to display the 'y' flag, just test if NewCount > 0.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 11:01:11

I feel like Lumbagos suggestion is what op want as it was suggested as follows

I'd like to add the 'new' column to the results too.
which means he wants new column also in resultset and not its count

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2011-09-30 : 13:41:00

So it works. I am able to go from initally looping through 200 cities to running a single SQL command and having my cities in order, the # of locations per each city AND the number of new locations in each city.

strSQL = "SELECT closest, COUNT(*) AS CityCnt, SUM(CASE new WHEN 'y' THEN 1 ELSE 0 END) AS NewCount from sites GROUP BY closest ORDER by closest"

dnew = objrs("newcount")
if dnew <> 0 then response.write " <font size=1 face=arial color=yellow> " & dnew & " LOCATIONS "

You guys are amazing (or I am just really SQL illiterate).

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-09-30 : 13:48:45

If you're going to be looping/movenext over more than a few hundred rows, learn about GetRows and GetString:

http://www.w3schools.com/ado/met_rs_getrows.asp
http://www.w3schools.com/ado/met_rs_getstring.asp

It's an absolute must if you'll be looping over 1000+ rows in a Recordset, the performance difference is astounding.

cmbergin
Starting Member

14 Posts

Posted - 2011-09-30 : 15:37:54

Seconded. When I did classic ASP, I *found* ways to use GetString, and you can pretty much always use GetRows. One tip for that - create a set of "constants" (VB Script doesn't really do constants) representing your row positions, i.e., I_Closest = 0, I_CityCount = 1, etc, and maintenance will be much easier.

oap
Yak Posting Veteran

60 Posts

Posted - 2011-09-30 : 20:49:55

I'll read up on Getrows/Getstring :)
I've always felt my approach was not the proper one, looping through 200 locations one by one.

I've worked it down to this now, with your help.

strSQL = "SELECT closest, COUNT(*) AS CityCnt, SUM(CASE new WHEN 'y' THEN 1 ELSE 0 END) as NewCount,"
strsql = strsql & " SUM(CASE new when 'u' THEN 1 ELSE 0 END) as NewPics from sites GROUP BY closest ORDER by closest"

closest = names of cities (the closest one to each location)
new = status of location (y = new location, n = not a new location, u = new photos)

It is working perfect, counting new locations and photos for each city. As you can see I've added the NEWPICS object which uses the SAME table ('new') as the previous SUM command.

My question is can they be combined? The 'new' record will only ever be Y,N or U for each location - not a combination of them.

eg.
SUM(CASE new when 'y' THEN 1 ELSE 0
when 'u' then 1 as NewPics END)
as NewCount

I know that won't work but you can get what I'm trying to do :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-01 : 12:12:57

do you mean this?

SUM(CASE when new in ('y','u') THEN 1 ELSE 0 END)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources