CODEWITHSUNDEEP
selectmysqlcount
developer
developer

Reputation: 2050

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

Upvotes: 28

Views: 78190

Answers (5)

zero8
zero8

Reputation: 2005

Once you already created a select statement

there is already a num_rows record of your count as num_rows

Upvotes: 1

HardQuestions
HardQuestions

Reputation: 4375

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

Upvotes: -1

Cristian Boariu
Cristian Boariu

Reputation: 9621

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)

Upvotes: 32

Peter Lang
Peter Lang

Reputation: 55524

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.

Upvotes: 20

Anwar Chandra
Anwar Chandra

Reputation: 8638

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

Upvotes: 1

Related Questions