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

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

Answer 2

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 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

My Solution:

SELECT COUNT(1) over(partition BY text) totalRecordNumber
  FROM (SELECT 'a' text, id_consult_req
          FROM consult_req cr);