CODEWITHSUNDEEP
mysqlasp-classic
TheCarver
TheCarver

Reputation: 19713

MySQL / Classic ASP - Record Count

I'm trying to add a count of all returned records in to my query, but having some issues. It's a very large query so I won't post it all.

When this executes, I get all records but the count rs.Fields("totalRecCount") always returns with 1. Can anybody see what I am doing wrong and suggest a fix?

This is the top part:

Set cmdConn = Server.CreateObject("ADODB.Command")
Set cmdConn.ActiveConnection = Conn
cmdConn.Prepared = True

Const ad_varChar = 200
Const ad_ParamInput = 1
Const ad_Integer = 3

selectClause = "SELECT COUNT(photoID) AS totalRecCount, photoID FROM photoSearch "
whereClause = "WHERE photoStatus = 'Live' "

This is the bottom part:

groupBy = "GROUP BY photoID "
orderClause = "ORDER BY dateCreated DESC "

SQL = selectClause & whereClause & groupBy & orderClause & limitClause
cmdConn.CommandText = SQL

Set rsPhotoSearch = cmdConn.Execute

Upvotes: 1

Views: 2179

Answers (2)

p.campbell
p.campbell

Reputation: 100567

Try this:

selectClause = "SELECT COUNT(*) AS totalRecCount, photoID FROM photoSearch "
whereClause = "WHERE photoStatus = 'Live' "   
groupBy = "GROUP BY photoID "
SQL = selectClause & whereClause & groupBy
cmdConn.CommandText = SQL

Upvotes: 0

Eric
Eric

Reputation: 95133

It returns 1 because you're grouping by the very thing you want to count. Remove your group by clause and the photoId column, and you'll get your record count.

The group by is telling MySQL, "For each distinct PhotoID, count the number of non-null PhotoIDs." By definition, that's 1.

Upvotes: 2

Related Questions