select count and other recors in one single query

Question

i have the following query

select main_cat_name,cat_url from mf_main order by main_cat_name

this returns whole data of my table.Now i want to have count of the total rows of this table.I can do it using another query but how can i use them in one single query??? i want two data ONE :- the rows of the table TWO:- the count how can i have that in one single query

I tried this but it gives correct count but displays only first row of the table :

select count(cat_id),main_cat_name,cat_url from mf_main order by main_cat_name

plz help!!

Answer 1

You can try with Group By like:

SELECT count(cat_id), main_cat_name, cat_url FROM mf_main GROUP BY main_cat_name, cat_url ORDER BY main_cat_name

Right solution i hope:

This is what you want:)

SELECT x.countt, main_cat_name, cat_url FROM mf_main, (select count(*) as countt FROM mf_main) as x ORDER BY main_cat_name

If you use mysql u have "as" like i did. For others db may be without as (like oracle)

Answer 2

select count(cat_id), main_cat_name, cat_url 
from mf_main
group by main_cat_name, cat_url
order by main_cat_name

Answer 3

You could try

select main_cat_name,cat_url,
COUNT(*) OVER () AS total_count
from mf_main
order by main_cat_name

Not sure if MySQL accepts the AS, just remove it if it does not.

Answer 4

If you don't use WHERE query and don't limit the output any other way, like using GROUP BY, than the rows amount in result will match the rows amount in table, so you can use database driver specific methods to find the rows count, e.g. mysql_num_rows in PHP