Hi,

I want to count the number of records that, for example contain 'client' and 'NULL' using one query instead of lots of separate queries.

Here is my attempt at the query but it doesn't work, can anyone show me the correct mysql syntax?

PHP Code:

 
SELECT status,
COUNT(status='client') AS "client",
COUNT(status='NULL') AS "not assigned"
FROM people
GROUP BY status 


Don't have MySql so these are not tested but you could do it using subquery if you need the column names as follows:

select 'clientcount'=(
select COUNT(*)
from people
where status='client'
),
'not assigned'=(
select COUNT(*)
from people
where statusisnull
)
from people

or like this if column name not important:

SELECT status,
COUNT(*) AS statuscount
FROM people
WHERE status='client' or status is null
GROUP BY status

Thanks for your reply vividearth I tried both of the queries but they didn't return the count for each data item stored in the status column.

The first query returned two columns each with the value of 1 for each record

PHP Code:

clientcount=select count(*) | not assigned=select(*)... 


The second query returns a column called status and statuscount but both contain no data. Is status a reserved word?

The status column only contains, NULL, client, closed and I want to count how many records have NULL, and how many in client etc

Would it be really slow to use three seperate count queries?

use a UNION query

select status,count(*) as rec from people where isnull(status) or status = "" group by status
union
select status,count(*) as rec from people where status = "closed" group by status
union
select status,count(*) as rec from people where status = "client" group by status

That query did exactly what I wanted, thanks chrishirst I was going to ask how to print the query results using PHP because I didn't know how to output data from a query that has different results for the same column name but I figured it out quickly:

PHP Code:

$query Count(*) ...
 
while($getPeopleIds->fetchInto($getPeopleId) {
 
echo $getPeopleId['status'];
echo $getPeopleId['rec'];
echo "<br>";
 
} 


Actually I'm surprised the answer hasn't come up yet.

Select Status, Sum(ClientCount) As Clients, Sum(NullCount) As Nulls From (
   Select
      Case When Status = 'Client' Then 1 Else 0 End As ClientCount,
      Case When Status Is Null Then 1 Else 0 End As NullCount,
      Status
   From People
   ) As InnerQuery
Group By Status

The advantage of this is efficiency. Once you start running subqueries and unions, you're parsing the table multiple times, in slices. Without good indexing, it forces multiple table scans. With proper indexes, it only adds management overhead. The way I posted, above, is purely set based, and will force the table to be read once, with all parsing happening inline.

I just realized - you're asking for a count by value for one column, not counts within other slices. The query I posted would be great if you wanted to know how many null vs "client" values were in the status column rated by age, zip code, or whatever.

Actually the easy answer seems to be

Select Status, Count(*) From People Group By Status

This will return a table like

Status - Count
Null - 23
Client - 15
Some Other Value - 92
Yet Another Value - 1

You can always rely on Learning Newbie to show you the most efficient queries

Originally Posted by drew22299

You can always rely on Learning Newbie to show you the most efficient queries

It's what I do - for a living. I realize half the time when someone asks a question like this, they don't need the absolute best performance. But I never know when they do or when they don't, and if a web site with server side code and also queries a database per request, once you start having more than a handful of visitors, performance becomes scalability. So I figure it's important.