CODEWITHSUNDEEP
mysqlsql
Joe
Joe

Reputation: 15349

Is it wrong to have count(*) as part of the select query

select id, first_name, count(*) from users;

The users table contains 10 entries, but the above select query shows only a single row. Is it wrong to mix count(*) as part of the above query?

Upvotes: 0

Views: 192

Answers (7)

Michael J.V.
Michael J.V.

Reputation: 5609

If you need number of rows in a table, you can use MySQL's SQL_CALC_FOUND_ROWS clause. Check MySQL docs to see how it's used.

Upvotes: 0

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

COUNT is an aggregate function and it will always give you count of all records in table unless used in combination with group by.

If you use it in combination with normal query, then it will take priority in deciding the final output as in your case it returns 1.

If you want to return all 10 records, you should just write -

select id,first_name from users

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56377

select id, first_name from users,(select count(*) as total from users) as t;

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29629

It's not "wrong", but it is meaningless without a "group by" clause - most databases will reject that query, as aggregate functions should include a group by if you're including other columns.

Upvotes: 2

Justin Wignall
Justin Wignall

Reputation: 3510

Not sure exactly what you're trying to achieve with this?

select id, first_name,(select count(*) from users) AS usercount from users;

will give each individual user and the total count but again, not sure why you would want it.

Upvotes: 1

halfdan
halfdan

Reputation: 34214

COUNT is a function that aggregates. You can't mix it into your normal query.

If you want to receive the ten entries just do a normal select:

SELECT id, name FROM users;

and to get the number of entries:

SELECT COUNT(id) FROM users;

Upvotes: 2

WraithNath
WraithNath

Reputation: 18013

Its becuase you are using an aggregate function in the select part of the query,

to return the 10 records you just need the id, and first_name in the query.

EG:

SELECT id, first_Name
FROM users

if you wanted to get a count of the records in the table then you could use

SELECT (Count(id))
FROM [users]

Upvotes: 2

Related Questions