Multiple record counts in one MySql statement

I've been creating some reports recently and been needing a lot of count information. The below statement pulls multiple counts in one SQL statement.

n.b. This is for MySQL and will probably be different for other db's

view plain print about

1SELECT
2(SELECT count(*) FROM tblName) AS 'Total Records',
3(SELECT count(*) FROM tblName WHERE col = 'a') AS 'Count'

Posted: 09-Feb-2006

Permalink: here

Comments

I just recently discovered this not too long ago, but I found that it only works with MySQL 4.x. My internal question, about doing using multiple SELECT statements, is, what about performance?

#1 ross
09/Feb/06 3:23 PM

If you are using SQL Server, there is a faster way:

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('<table_name>') AND indid < 2

Using COUNT(*) can have some really bad performance issue when the table is big. I would definatly read the following website for some performace tips:

http://www.sql-server-performance.com/misc_tips.as...

#2 tony petruzzi
10/Feb/06 2:14 PM

An alternative I discovered today is to use GROUP and WITH ROLLUP - e.g.

SELECT
   COUNT(*) as count,
   col
FROM
   table
GROUP BY
   col
WITH ROLLUP

you'll get a count of your different 'col' values, and a total on the last row

#3 sam
30/Nov/06 5:08 PM

"
SELECT
(SELECT count(*) FROM tblName) AS 'Total Records',
(SELECT count(*) FROM tblName WHERE col = 'a') AS 'Count'
"
this code also works for MySQL 5.x

#4 Razvan
28/Feb/08 8:25 AM

can u post or email me a full query using this method please?

i cant get it to work...

#5 jason
08/Apr/08 9:24 AM

actually, dont worry, i have figured out my mistake!!

thanks you soooooooo much for this tip!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

!!!

#6 jason
08/Apr/08 9:30 AM

Thanks lot of,
It is very helpful me.

#7 Yasa
15/Nov/10 7:35 AM

Andy Jarrett

Multiple record counts in one MySql statement

Comments