CODEWITHSUNDEEP
sqlsql-servercount
Beeblebrox
Beeblebrox

Reputation: 253

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?

Upvotes: 0

Views: 73

Answers (2)

S K
S K

Reputation: 11

@Beeblebrox, what Tim suggested is correct. Based on that I made a few below changes.

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.CustId = S.CustId And E.Type In ('x', 'y')
Group By C.CustId, C.Name

See the result

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

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;

Upvotes: 3

Related Questions