CODEWITHSUNDEEP
mysqlsql
Super Hornet
Super Hornet

Reputation: 2907

counting rows of selected data in mysql

I'm going to count returned rows by this query:

 select * from tableName where( number > 1000);

I tried this query to get rows count:

 select count(*) as count from (select * from tableName where( number > 1000));

but I got syntax error. What is wrong with that?

Upvotes: 0

Views: 98

Answers (8)

Rivnat
Rivnat

Reputation: 1507

This works if you are using nested query & don't use 'count' as your temporary variable name:

select count(temp.id) as num from (select * from tableName where number > 1000) temp

Upvotes: 2

Mad Angle
Mad Angle

Reputation: 2330

Make it simple. Don't need a subquery

select count(*) as count from tableName where number > 1000;

Upvotes: 1

Navneet
Navneet

Reputation: 447

This query works fine

select count(*) as count from tableName where number > 1000 ;

but FYR in ur query error is u don't define subquery name in example its tbl

select count(*) as count from (select * from tableName where( number > 1000)) as tbl;

Upvotes: 0

Smruti Singh
Smruti Singh

Reputation: 565

FIrst thing first

SELECT COUNT(*) as tot_rows FROM `tableName` WHERE  `number` > 1000 ;

you better give back tilt (`) sign arond number, cause might be there's a keyword in mysql called number. I am not pretty sure, but you should take precaution.

Second you can do another thing also.

If you are using PHP then

$query_result = mysql_query("SELECT * FROM `tableName` WHERE  `number` > 1000");

$num_row = mysql_numrows($query_result);

Upvotes: 0

spencer7593
spencer7593

Reputation: 108370

Likely, the syntax error your are getting is "every derived table must have an alias".

To fix that syntax error, you would just assign an alias to the inline view query.

SELECT foo FROM (SELECT foo FROM bar) a ;
                                      ^

But for your specific query, an inline view isn't required.

You could simply modify your original query, to replace the * in the SELECT list with an aggregate expression such as COUNT(*). You can also assign an alias to the aggregate expression.

It's valid to use COUNT as a column alias, but my preference would be to use a different alias, one that isn't the name of a MySQL function.

Upvotes: 0

Lasitha Benaragama
Lasitha Benaragama

Reputation: 2209

You dont want Nested Query Just Use 

select count(*) as count from tableName where number > 1000 ;

Upvotes: 4

jondinham
jondinham

Reputation: 8511

Why not counting the rows in 1 select directly:

select count(*) from tableName where number>1000;

Upvotes: 1

Thusitha Thilina Dayaratne
Thusitha Thilina Dayaratne

Reputation: 5712

It should be like this

select count(*) as noOfCount from tableName where number > 1000;

Do not use sql reserved keywords as your temp variable names

Upvotes: 1

Related Questions