select count and other records 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 

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

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.