Number of rows

mikerh9

This is my query
select id, status from sale where user_id = 1 GROUP BY id;

I need to know how many rows or how many entries this query returns. I
tried to use "count" and "sum". But the result is wrong.

I want to be able to make select and know how many rows this query
returns. Now, I use mysql_num_rows in php,

Roger_Ramjet

In SQL

SELECT count(user_id) as count FROM sale WHERE user_id=1 GROUP BY user_id

If you include any other fields that have different values, like id, then the grouping will be broken up and you will not get the correct count.

mikerh9

Originally posted by Roger Ramjet
In SQL

SELECT count(user_id) as count FROM sale WHERE user_id=1 GROUP BY user_id

If you include any other fields that have different values, like id, then the grouping will be broken up and you will not get the correct count.

But I want in one query get count and other fields. Is it possible?

Roger_Ramjet

http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html

select id, status, count(*) as count from sale where user_id = 1 GROUP BY id;

nemonoman

Sorry that won't work roger; if there were 22 sales rows for user_id 1 your query will only return a single row something like:
'1', 'in process' , 22

You can't both get the total count and individual row values in a single stroke.

After running your query, a mysql function
[man]mysql_num_rows[/man] will tell you how many rows were selected.

Roger_Ramjet

Quite true nemonoman, not really paying attention. Or rather, misunderstood the following from the manual:

"COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values."

My first answer still stands.

nemonoman

AND your first answer is entirely reasonable. However, the SELECT count(*) approach takes a separate query and a separate fetch; the mysql_num_rows requires only making a call to the function. That should save at least 7 or 8 nanoseconds...