developer developer - 1 year ago 42
SQL Question

select count and other records in one single query

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 Source

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)