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
– Jason S, Commented May 20, 2009 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
– Random Developer, Commented May 20, 2009 at 16:03

Community · Accepted Answer · 2017-05-23 11:46:13Z

26

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, 2017 at 11:46

CommunityBot

11 silver badge

answered May 20, 2009 at 15:52

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason S Over a year ago

The self-join handles the case where there are other joins in the conditions.

Andomar · Accepted Answer · 2009-05-20 16:12:33Z

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, 2009 at 16:12

answered May 20, 2009 at 15:47

Andomar

239k55 gold badges387 silver badges412 bronze badges

7 Comments

kscott Over a year ago

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

Paul Suart Over a year ago

I like this technique, it's elegant.

Jason S Over a year ago

is that syntax "Alias = Expression" particular to SqlServer/MySQL or is it fairly general?

Andomar Over a year ago

It's pretty SqlServer specific, I'll change it

Cloudy Over a year ago

I wouldn't have thought of using Sum() like this. +1

|

Andomar · Accepted Answer · 2009-05-20 16:02:34Z

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, 2009 at 16:02

Andomar

239k55 gold badges387 silver badges412 bronze badges

answered May 20, 2009 at 15:54

Middletone

4,27612 gold badges57 silver badges74 bronze badges

2 Comments

Random Developer Over a year ago

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.

kscott Over a year ago

This query will get you the answer, but the cost of two select statements and the where clause make the CASE solution more efficient

Ryu S. · Accepted Answer · 2020-04-09 19:59:48Z

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 9, 2020 at 19:59

Ryu S.

2,1013 gold badges28 silver badges52 bronze badges

Comments

Craig Lewis · Accepted Answer · 2009-05-20 16:57:47Z

0

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;

answered May 20, 2009 at 16:57

Craig Lewis

3312 silver badges3 bronze badges

Collectives™ on Stack Overflow

two SQL COUNT() queries?

5 Answers 5

1 Comment

7 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

7 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related