CODEWITHSUNDEEP
mysqlsql
elite5472
elite5472

Reputation: 2184

Count occurrences of a field for each group?

I'm doing a pretty complicated query, and I'm slipping at the very last step.

I have managed to get this far:

id  refid           system_id   item_description_id   system_component_id   current_status
711 4fb62cece5313   49          NULL                  711                   RUNNING
712 4fb62cece547d   49          NULL                  712                   STOPPED
713 4fb62cece5616   50          NULL                  713                   RUNNING
714 4fb62cece5803   50          NULL                  714                   STOPPED
716 4fb62cece5ab8   51          NULL                  716                   RUNNING

These are the current statuses of every component of each system. What comes next is to group them by system and count the occurrences that are 'STOPPED'.

My problem is that some systems will not have any rows with 'STOPPED', or any rows at all if the system has never been updated. Yet, I still have to get them as part of the result. Just grouping them by system works for all but these two cases, in which the resulting value is '1' even though there are no stopped components at all.

How can I make a query that will return something like this:

system_id    status_count
49           1
50           1
51           0

And not:

system_id    status_count
49           1
50           1
51           1

With the above table?

Upvotes: 1

Views: 151

Answers (2)

Ankit Sharma
Ankit Sharma

Reputation: 4071

select system_id, 
IF( if(currentstaus = 'RUNNING',0.5,0) + if(currentstaus = 'STOPPED',0.5,0)=1,1,0) 
AS status_count from table_name group by system_id

Try this query and tell me if it works or not.

Upvotes: 1

Michael Buen
Michael Buen

Reputation: 39393

You have to use SUM(boolean), like this:

select system_id, 
  sum(current_status = 'STOPPED') AS stopped_count, 
  sum(current_status = 'RUNNING') as running_count
from tbl
group by system_id

Boolean is automatically reduced to integer in MySQL

Output:

| SYSTEM_ID | STOPPED_COUNT | RUNNING_COUNT |
|-----------|---------------|---------------|
|        49 |             1 |             1 |
|        50 |             1 |             1 |
|        51 |             0 |             1 |

Live test: http://www.sqlfiddle.com/#!2/d3bcc/2

If the COUNT(or any aggregation function for that matter) come from LEFT JOIN, you have to enclose the results with COALESCE or ISNULL:

select system_id, 
  isnull( sum(current_status = 'STOPPED') , 0 ) AS stopped_count, 
  isnull( sum(current_status = 'RUNNING') , 0 ) as running_count
from tbl
group by system_id

ANSI SQL-compliant:

select system_id, 
  coalesce( sum(current_status = 'STOPPED') , 0 ) AS stopped_count, 
  coalesce( sum(current_status = 'RUNNING') , 0 ) as running_count
from tbl
group by system_id

Some database are a little verbose though. Postgresql: http://www.sqlfiddle.com/#!1/d3bcc/2

select system_id, 
  sum((current_status = 'STOPPED')::int) AS stopped_count, 
  sum((current_status = 'RUNNING')::int) as running_count
from tbl
group by system_id

Other database: http://www.sqlfiddle.com/#!3/d3bcc/2

select system_id, 
  count(case when current_status = 'STOPPED' then 1 end) AS stopped_count, 
  count(case when current_status = 'RUNNING' then 1 end) as running_count
from tbl
group by system_id

Upvotes: 3

Related Questions