adding count( ) column on each row

Question

I'm not sure if this is even a good question or not.

I have a complex query with lot's of unions that searches multiple tables for a certain keyword (user input). All tables in which there is searched are related to the table book.

There is paging on the resultset using LIMIT, so there's always a maximum of 10 results that get withdrawn.

I want an extra column in the resultset displaying the total amount of results found however. I do not want to do this using a separate query. Is it possible to add a count() column to the resultset that counts every result found?

the output would look like this:

ID    Title    Author    Count(...)  
1     book_1   auth_1      23  
2     book_2   auth_2      23  
4     book_4   auth_..     23  

...

Thanks!

Answer 1

The usual way of counting in a query is to group on the fields that are returned:

select ID, Title, Author, count(*) as Cnt
from ...
group by ID, Title, Author
order by Title
limit 1, 10

The Cnt column will contain the number of records in each group, i.e. for each title.

Answer 2

You simply cannot do this, you'll have to use a second query.

Answer 3

Regarding second query:

select tbl.id, tbl.title, tbl.author, x.cnt
from tbl
cross join (select count(*) as cnt from tbl) as x

If you will not join to other table(s):

select tbl.id, tbl.title, tbl.author, x.cnt
from tbl, (select count(*) as cnt from tbl) as x

Answer 4

If your problem is simply the speed/cost of doing a second (complex) query I would suggest you simply select the resultset into a hash-table and then count the rows from there while returning, or even more efficiently use the rowcount of the previous resultset, then you do not even have to recount

Answer 5

This won't add the count to each row, but one way to get the total count without running a second query is to run your first query using the SQL_CALC_FOUND_ROWS option and then select FOUND_ROWS(). This is sometimes useful if you want to know how many total results there are so you can calculate the page count.

Example:

select SQL_CALC_FOUND_ROWS ID, Title, Author
from yourtable
limit 0, 10;
SELECT FOUND_ROWS();

From the manual: http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows