CODEWITHSUNDEEP
sql
Jason S
Jason S

Reputation: 189746

two SQL COUNT() queries?

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.

Upvotes: 12

Views: 35416

Answers (5)

Ryu S.
Ryu S.

Reputation: 1989

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);

Upvotes: 1

Craig Lewis
Craig Lewis

Reputation: 331

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;

Upvotes: 0

Andomar
Andomar

Reputation: 238126

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.

Upvotes: 21

Guffa
Guffa

Reputation: 700432

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

Related:

SQL Combining several SELECT results

Upvotes: 26

Middletone
Middletone

Reputation: 4270

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

Upvotes: 7

Related Questions