SQL count distinct column with row value-CodePudding

I have the table tbl_rating which contains survey results with the following structure:

| office_name | rating      |
|-------------|-------------|
| Office1     | Satisfied   |
| Office3     | Unsatisfied |
| Office2     | Neutral     |
| Office1     | Satisfied   |

I'm having trouble having my query display all DISTINCT values of the office_name column with the total number of the same value from the rating column.

The result from the query I am looking for is the following:

| office_name | Satisfied | Neutral | Unsatisfied |
|-------------|-----------|---------|-------------|
| Office1     | 2         | 0       | 0           |
| Office2     | 0         | 1       | 0           |
| Office3     | 0         | 0       | 1           |

this is query that i'm working with at the moment. it works for counting total number of rating of the declared value, but does gives me an error when I tried to display the result of the array using DISTINCT office

$office = $db->query("SELECT DISTINCT office
(SELECT COUNT(rating) FROM tbl_result WHERE rating='Satisfied') AS S,
(SELECT COUNT(rating) FROM tbl_result WHERE rating='Neutral') AS N,
(SELECT COUNT(rating) FROM tbl_result WHERE rating='Unsatisfied') AS U");

CodePudding user response：

SELECT
  office_name,
  SUM(IF(rating = 'Satisfied', 1, 0)) AS Satisfied,
  SUM(IF(rating = 'Neutral', 1, 0)) AS Neutral,
  SUM(IF(rating = 'Unsatisfied', 1, 0)) AS Unsatisfied
FROM
  tbl_rating
GROUP BY
  office_name

Edited (thanks to sticky bit for his remarks):

removed DISTINCT clause
changed double quotes around strings to single quotes

sqlfiddle