组表值并将其他值相加

Question

I have a mysql table with messages in. Each message has a status on its current status in the system. We want to generate charts comparing open messages vs the rest. I am trying to create a JSON response that looks as follows:

Roofing: [
   open: 12,
   others: 24
],
Building: [
   open: 4,
   others: 32
]

This is how the mysql database looks

+---+------------+---------+
|ID |  Industry  | Status  |
+---+------------+---------+
| 1 |  Building  | Sent    |
| 2 |  Building  | Review  |
| 3 |  Building  | Open    |
| 4 |  Roofing   | Sent    |
| 5 |  Roofing   | Review  |
| 6 |  Roofing   | Open    |
+---+------------+---------+

Sent and Review all fall under others

I need a way to count all the values for open and others and then lay it out in JSON.

Thanks for any help in advance.

Answer 1

An often overlooked feature of COUNT is that it actually counts NON null values only

SELECT industry, 
  COUNT(CASE WHEN Status  ='Open' THEN 1 ELSE NULL END) as open_count, 
  COUNT(CASE WHEN Status !='Open' THEN 1 ELSE NULL END) as other_count

  FROM Table1 GROUP BY industry