Q:

Retrieving multiple counts from a table per row returns incorrect count results

I am trying to show a list of customers and the number of ratings they've left by type. However the returns from counts are not accurate.
This is happening in SQL Server.
I have put together a representative schema and test data here: http://sqlfiddle.com/#!9/97908f3/5
select customer.custid, customer.name,
(
  select count(eventid) from event
  where type in ('x', 'y')
  and rating = 'good'
  and event.stayid = stay.stayid
) as 'Goods',
(
  select count(eventid) from event
  where type in ('x', 'y')
  and rating = 'ok'
  and event.stayid = stay.stayid
) as 'OKs',
(
  select count(eventid) from event
  where type in ('x', 'y')
  and rating = 'bad'
  and event.stayid = stay.stayid
) as 'Bads'
from customer, stay
where stay.custid = customer.custid

The results show:
custid  name    Goods   OKs Bads
1       Jane    1       0   1
2       Alice   1       0   1

According to the data, Alice shouldn't have the 1 good review.
I have tried the same query as joins but get the same issue.
What am I doing wrong when I am trying to query the same table for multiple counts per row?

A:

You should be using a single query with conditional aggregation here:
SELECT
    c.custid,
    c.name,
    COUNT(CASE WHEN e.rating = 'good' THEN 1 END) AS Goods,
    COUNT(CASE WHEN e.rating = 'ok'   THEN 1 END) AS Oks,
    COUNT(CASE WHEN e.rating = 'bad'  THEN 1 END) AS Bads
FROM customer c
LEFT JOIN stay s
    ON s.custid = c.custid
LEFT JOIN event e
    ON e.stayid = s.stayid AND
       e.type IN ('x', 'y')
GROUP BY
    c.custid,
    c.name;

