Display the total sum of rows found

I'm sure there is an easy answer to this question, it's just I couldn't find it in any of the books that I own.

If I do a SELECT statement that finds say 1000 rows that have been matched, is it possible to return the sum of rows found, i.e. 1000 and at the same time LIMIT the rows returned to 20.

Basiclly, I want to show the user 20 rows at a time but also the total number of rows found, i.e 1000. I hope I have explained things clear enough.

Many thanks in advance.I don't think it's possible in one statement. You have to run 2 statements:

select count(*)from table;

select * from table where rownum <= 20;

or you can have one additional column in your result filled with the count

select
(select count(*) from table),
t.*
from table t
where rownum <= 20;

or have one additional row, but you have to know how many columns you have in your result. e.g. for 4 columns

selce count(*), count(*), count(*), count(*) from table
union all
select * from table
where rownum <= 20;|||Thank you very much for your prompt reply.

With a little bit of tweaking, I managed to get the following statement to work exactly how I like. Basically, out of a table of 3000 rows, it returns 1300 rows, of which I want to display 20 rows only plus at the end of the table one extra row of the count (in this case 1300).

SELECT product, price from electronics
WHERE sid = '187'
LIMIT 20
UNION ALL
SELECT count(*), count(*) from electronics
WHERE sid = '187'

However, as I want to eventually do a sort on the 1300 rows returned, I think I would like to have an extra column of the count instead of an extra row of the count, i.e. 'product', 'price', 'count'. I know it's inefficient to have an extra colomn all with the same sum but I don't really want to do a sort and then mix the count in with it. A solution to adding an extra column with the count would be much appreciated.

Or, is it possible to have the above working select statement but with the count as the first row, and then somehow extract the first row using perl before I do a sort on the 1300 rows returned.

Thanking you again.|||I'm not sure, but I think, it could work when you first select count(*) then UNION ALL your select ordered by whatever. Do not order final result which includes count.

SELECT count(*), count(*) from electronics
WHERE sid = '187'
UNION ALL
SELECT product, price from electronics
WHERE sid = '187'
ORDER BY price DESC
LIMIT 20 -- I don't know if this goes before ORDER BY|||now I had a chance to test my last suggestion (previous post). You'll have to use something like:

SELECT count(*) product, count(*) price from electronics
WHERE sid = '187'
UNION ALL
SELECT product, price from
(SELECT product, price from electronics
WHERE sid = '187'
ORDER BY price DESC
LIMIT 20 -- I don't know if this goes before ORDER BY
) X;

otherwise most likely your count won't be in first line. the problem is some databases don't allow ORDER BY in views, subqueries, derived tables...|||Again thank you for your replies. Unfortunatly, I can't seem to get your examples to work. I've managed to come up with the statement below which does exactly what I want i.e. with the count as the first row, this is only possible because I've set the 2nd column of the count row to 0.00, and hopefully as there will never be a product whose price is 0.00 then when I do a ASC sort it should always become the first row.

Additionally, as the count is a number and is in the product column (products are normally described in words), a ASC sort should always put that as the first row.

Of course everything falls down if a product is described as a number or it's price is 0.00, rare but could happen.

SELECT product, price from electronics
WHERE sid = '187'
UNION ALL
SELECT count(*), '0.00' from electronics
WHERE sid = '187'
ORDER BY price ASC
LIMIT 20

I'm sure there is a proper solution, just haven't found it yet. What I really want is to have an extra column with the count in it, i.e.

Table has 3000 rows, rows returned from search is 1300

Product Price Count
--------------
JVC AV27D202 125.00 1300
Panasonic CT32SF37 150.00 1300
Pioneer PDP505HD 200.00 1300
Sharp 27NS100 250.00 1300
etc

instead of

Product Price
----------
1300 0.00
JVC AV27D202 125.00
Panasonic CT32SF37 150.00
Pioneer PDP505HD 200.00
Sharp 27NS100 250.00
etc

By the way, my database is MySQL. Many thanks.|||You could use negative price (like -1.00), and NULL description for your count, it should be on the top allways (row solutions).

did you also try something like:

SELECT
product,
price,
(SELECT count(*) from electronics WHERE sid = '187') cnt
from electronics
WHERE sid = '187'
LIMIT 20

this could be your "column" solution.
what DB do you use?|||My database is MySQL. Great idea about the negative price and NULL. I shall use the row solution if nothing else works.

The column solution however doesn't seem to work, although I can see what you are doing. It falls down I think because it doesn't like the syntax "(SELECT count(*) from electronics WHERE sid = '187') cnt" as a means of creating a third column. Both select statements work perfectly on their own but not together.

Many thanks.|||yes, I think problem is: MySQL doesn't support subqueries. Maybe it's not true in newer versions, but I'm sure this feature wasn't supported by some older versions