SQL - counting based on multiple criteria

Question

I've written SQL count statements before but I need to write a query which returns 2 sets of count values for a condition. The original query counts the amount of people for a company who have invalid information, fine, works great. I now need to extend this query so that it performs the above operation but also includes the total count of people.

So for instance, I'll get a result for Company A of 5 invalid people out of a total of 10.

I'm using a group by on my first query on company id.

Is it possible to include 2 count values?

Edit 1

Probably should have said, I'm using MySQL.

Answer 1

you'd need to have the invalid count in a case statement in the select clause instead of in the where clause

Answer 2

Without table structure, made assumptions:

  SELECT t.company_id,
         SUM(CASE WHEN t.isinvalid = 1 THEN 1 ELSE 0 END) AS numInvalid,
         COUNT(*) AS total
    FROM YOUR_TABLE t
GROUP BY t.company_id

Answer 3

depending on your RDBMS - there are different solutions. In SQL Server/Oracle - you can use OVER() clause.

http://msdn.microsoft.com/en-us/library/ms189461.aspx

Example:

select count(PersonID) over() as TotalCount, count(personID) over(PARTITION BY CompanyID) FROM Table

Answer 4

Figured this problem out doing a left join and counting based on those results.