Question 245599 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I sum two group by outcomes in MYSQL

I am trying to obtain the following result:
Imagine I have an object table in the following format

Name  Object id Status
A A. . On
A B. . Off
A C. . Other
B D. . On
B E. . Other
B F. . Other

And I want the following result

Name. count of objects with status On or Off
A 2
B 1

In words, my goal is, for each name, to count the number of objects by status, which I can easily do with a group by ‘Status’ query, but to sum two of the possible statuses.

Thank you

Tags:

2

Answers


  1. 0

    Using sum:

    select t.name, sum(t.status in ('. On', '. Off')) c from tbl t group by t.name

    See fiddle

    Login or Signup to reply.
  2. 0

    You can use conditional aggregation.

    select 
 name, 
 sum(case when status in ('. On', '. Off') then 1 else 0 end) as count_of_objects
from table1
group by name
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search