Using COUNT with a HAVING Clause in MySQL

Question

We are having some trouble figuring out why we can't COUNT while using the HAVING clause at the end of our statement.

This statement returns results:

SELECT lid, cid, title, url, description, date, hits, downloadratingsummary, totalvotes, totalcomments, filesize, version, homepage, ns_compat, ns_des_img, ( (2.0 * (MATCH(title) AGAINST ('fsx concorde' IN BOOLEAN MODE))) + (1.0 * (MATCH(description) AGAINST ('fsx concorde' IN BOOLEAN MODE))) + (0.5 * (MATCH(url) AGAINST ('fsx concorde' IN BOOLEAN MODE))) ) AS relevance FROM nuke_downloads_downloads WHERE ( MATCH(title,description,url) AGAINST ('fsx concorde' IN BOOLEAN MODE) ) HAVING relevance > 3.0 ORDER BY relevance DESC

This statement returns nothing

SELECT COUNT(*) as num, lid, cid, title, url, description, date, hits, downloadratingsummary, totalvotes, totalcomments, filesize, version, homepage, ns_compat, ns_des_img, ( (2.0 * (MATCH(title) AGAINST ('fsx concorde' IN BOOLEAN MODE))) + (1.0 * (MATCH(description) AGAINST ('fsx concorde' IN BOOLEAN MODE))) + (0.5 * (MATCH(url) AGAINST ('fsx concorde' IN BOOLEAN MODE))) ) AS relevance FROM nuke_downloads_downloads WHERE ( MATCH(title,description,url) AGAINST ('fsx concorde' IN BOOLEAN MODE) ) HAVING relevance > 3.0 ORDER BY relevance DESC

Are we missing anything here?

Your help is greatly appreciated and thank you in advance.

We want to count the number of rows returned by the SQL command. The COUNT function works fine without the HAVING command at the end of the query. — Ted Wilmont
– Ted Wilmont, Commented Sep 20, 2013 at 13:42
Can't you do at the application (that receives the results)? From what I remember the usual PHP modules that send queries to MySQL have this feature already. Unless you want just a number, only the COUNT and not the results. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Sep 20, 2013 at 13:44
I can't remember how HAVING works, but don't you need to use GROUP BY in order to count the right thing? — Paco
– Paco, Commented Sep 20, 2013 at 13:49
Application level in PHP (mysql_num_rows) seems much slower (lots of CPU usage by PHP) and we would have to perform the query twice as we are also limiting the results for pagination of the results returned. Unless we can combine the limits in the SQL statement and then also perform a row count on all "available" results in one query? — Ted Wilmont
– Ted Wilmont, Commented Sep 20, 2013 at 14:12

ypercubeᵀᴹ · Accepted Answer · 2013-09-20 13:50:22Z

2

If you want only the count of rows that the first query would return, you can encapulate the query into a derived table.

You can skip all the columns except the relevance from the SELECT list and the ORDER BY:

SELECT COUNT(*)
FROM
  ( SELECT ( (2.0 * (MATCH ... ) AS relevance 
    FROM ...                                     -- unchanged
    WHERE ...                                    -- unchanged
    HAVING relevance > 3.0
                                                 -- ORDER BY removed    
  ) AS t ;

answered Sep 20, 2013 at 13:50

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using COUNT with a HAVING Clause in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related