two SQL COUNT() queries?

Question

I want to count both the total # of records in a table, and the total # of records that match certain conditions. I can do these with two separate queries:

SELECT COUNT(*) AS TotalCount FROM MyTable;
SELECT COUNT(*) AS QualifiedCount FROM MyTable
  {possible JOIN(s) as well e.g. JOIN MyOtherTable mot ON MyTable.id=mot.id} 
  WHERE {conditions};

Is there a way to combine these into one query so that I get two fields in one row?

SELECT {something} AS TotalCount, 
  {something else} AS QualifiedCount 
  FROM MyTable {possible JOIN(s)} WHERE {some conditions}

If not, I can issue two queries and wrap them in a transaction so they are consistent, but I was hoping to do it with one.

edit: I'm most concerned about atomicity; if there are two sub-SELECT statements needed that's OK as long as if there's an INSERT coming from somewhere it doesn't make the two responses inconsistent.

edit 2: The CASE answers are helpful but in my specific instance, the conditions may include a JOIN with another table (forgot to mention that in my original post, sorry) so I'm guessing that approach won't work.

MySQL but I would like to know it in general if it's pretty straightforward — Jason S, May 20 '09 at 15:54
Related but not to the solution: you can also use to get the counts for larger tables. SELECT SQL_CALC_FOUND_ROWSFROM MyTable; SELECT FOUND_ROWS(); SELECT SQL_CALC_FOUND_ROWSFROM MyTable WHERE {conditions}; SELECT FOUND_ROWS(); — Random Developer, May 20 '09 at 16:03

score 25 · Accepted Answer · edited May 23 '17 at 11:46

25

One way is to join the table against itself:

select
   count(*) as TotalCount,
   count(s.id) as QualifiedCount
from
   MyTable a
left join
   MyTable s on s.id = a.id and {some conditions}

Another way is to use subqueries:

select
   (select count(*) from Mytable) as TotalCount,
   (select count(*) from Mytable where {some conditions}) as QualifiedCount

Or you can put the conditions in a case:

select
   count(*) as TotalCount,
   sum(case when {some conditions} then 1 else 0 end) as QualifiedCount
from
   MyTable

edited May 23 '17 at 11:46

Community

1
1

answered May 20 '09 at 15:52

Guffa

687,336
108
737
1,005

The self-join handles the case where there are other joins in the conditions. – Jason S May 20 '09 at 16:05

Andomar · Answer 2 · 2009-05-20T16:12:33.427

21

In Sql Server or MySQL, you can do that with a CASE statement:

select 
    count(*) as TotalCount,
    sum(case when {conditions} then 1 else 0 end) as QualifiedCount
from MyTable

Edit: This also works if you use a JOIN in the condition:

select 
    count(*) as TotalCount,
    sum(case when {conditions} then 1 else 0 end) as QualifiedCount
from MyTable t
left join MyChair c on c.TableId = t.Id
group by t.id, t.[othercolums]

The GROUP BY is there to ensure you only find one row from the main table.

edited May 20 '09 at 16:12

answered May 20 '09 at 15:47

Andomar

232,371
49
380
404

1

Just want to point out that this example will perform much better than any union or join as it only has to evaluate the table in question once – kscott May 20 '09 at 15:55
I like this technique, it's elegant. – Paul Suart May 20 '09 at 15:56
is that syntax "Alias = Expression" particular to SqlServer/MySQL or is it fairly general? – Jason S May 20 '09 at 15:57
It's pretty SqlServer specific, I'll change it – Andomar May 20 '09 at 15:59
I wouldn't have thought of using Sum() like this. +1 – Cloudy May 20 '09 at 16:04
The join you listed works because it's a LEFT JOIN. It won't work if it's a JOIN since the # of rows in MyTable will be narrowed down, and I want to get the total # of rows in MyTable. But thanks for the attempt, it would work for LEFT JOINs. – Jason S May 20 '09 at 16:45
An INNER JOIN would narrow down the number of rows with a self-join too; the most restrictive join wins. – Andomar May 20 '09 at 17:38

score 7 · Answer 3 · edited May 20 '09 at 16:02

7

if you are just counting rows you could just use nested queries.

select 
    (SELECT COUNT(*) AS TotalCount FROM MyTable) as a,
    (SELECT COUNT(*) AS QualifiedCount FROM MyTable WHERE {conditions}) as b

edited May 20 '09 at 16:02

Andomar

232,371
49
380
404

answered May 20 '09 at 15:54

Middletone

4,190
12
53
74

I agree this is the better way to get the result as it gets you want and should give it to you in about the same time as the separate executions. – Random Developer May 20 '09 at 15:59
This query will get you the answer, but the cost of two select statements and the where clause make the CASE solution more efficient – kscott May 20 '09 at 16:02

score 1 · Answer 4 · answered Apr 09 '20 at 19:59

1

In Oracle SQL Developer I had to add a * FROM in my select, or else i was getting a syntax error:

select * FROM 
    (select COUNT(*) as foo FROM TABLE1),
    (select COUNT(*) as boo FROM TABLE2);

answered Apr 09 '20 at 19:59

Ryu S.

1,538
2
22
41

score 0 · Answer 5 · answered May 20 '09 at 16:57

MySQL doesn't count NULLs, so this should work too:

SELECT count(*) AS TotalCount, 
  count( if( field = value, field, null)) AS QualifiedCount 
  FROM MyTable {possible JOIN(s)} WHERE {some conditions}

That works well if the QuailifiedCount field comes from a LEFT JOIN, and you only care if it exists. To get the number of users, and the number of users that have filled in their address:

SELECT count( user.id) as NumUsers, count( address.id) as NumAddresses
  FROM Users
  LEFT JOIN Address on User.address_id = Address.id;

two SQL COUNT() queries?

5 Answers5

Linked