adding count( ) column on each row
mysql, sql
Solution
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
Problem
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!