COUNT() together with the rest of the table's columns

What's the easiest way to select the number of rows found, together with other columns from the 开发者_StackOverflow中文版table? The only way I've found is to do a join with a sub-query, but I imagine it's more resource heavy than such a simple task should be...

The result I'm looking for is

SELECT lots_of_rows, count(*)
FROM table
with some magic, so it doesn't reduce everything in one row.

f1  f2  f3    rowcount
----------------------
a   b   c     3
a   c   e     3
a   r   g     3

Or something like that.

Here's a way using an inline view, which will work even if your table doesn't have a primary key. By using an inline view, you don't have to use a join.

Note: This was done on Oracle, but I believe the SQL should work for MySQL or require minimal tweaking.

CREATE TABLE t1 (f1 CHAR(1), f2 CHAR(1), f3 CHAR(3));

INSERT INTO t1 VALUES ('a','b','c');
INSERT INTO t1 VALUES ('a','c','e');
INSERT INTO t1 VALUES ('a','r','g');

SELECT a.f1
     , a.f2
     , a.f3
     , b.cnt
  FROM t1 a
     , (SELECT COUNT(*) cnt from t1) b

Result:

    F1  F2  F3  CNT
1   a   b   c   3
2   a   c   e   3
3   a   r   g   3

You can do it without a subquery, the last row will hold all nulls and the total rowcount in field rowcount

SELECT lots_of_rows, count(*) as rowcount
FROM atable
GROUP BY atable.primary_key WITH ROLLUP

Another option might is:

SELECT lots_of_rows FROM atable;
SELECT FOUND_ROWS() as rowcount;

The second query will return instantly, because it just gives you the rowcount from the first query.

See: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows