Sp00kyy Sp00kyy - 6 months ago 13
SQL Question

SQL count categorized by different amounts

As the title suggests. I'm trying to write a query that will give me a count of all people who haven't attended something, however I then need to group them by how many times they haven't attended. Sort of like this.

|---------------------|-----------------------------------|
| No. Of People | No. of times not attended |
|---------------------|-----------------------------------|
| 12 | 1 |
|---------------------|-----------------------------------|
| 34 | 2 |
|---------------------|-----------------------------------|


In this sort of format, with the 1 meaning 'didnt attend once' and the 2 meaning 'didn't attend twice' etc etc.

This is what I have for now..

SELECT COUNT(p.PersonID)AS 'No. of People'
,COUNT(e.attended) AS 'Attended'
,et.EpisodeTypeName
FROM Person p
JOIN Episode e ON e.PersonID = p.PersonID
JOIN EpisodeType et ON et.EpisodeTypeID = e.EpisodeTypeID
WHERE e.Attended = 'No'
AND e.EpisodeDate >= '2015-04-01' AND e.EpisodeDate <= '2016-03-31'
GROUP BY e.Attended, et.EpisodeTypeName;


Any help with this would be great!

Answer

If I understand correctly, this is a histogram-of-histograms query. Also, I don't see that EpisodeType is needed, at least based on the results and query in the question.

So, a query with two levels of aggregation:

SELECT NotAttended, COUNT(*) as NumPeople
FROM (SELECT p.PersonID, COUNT(*) as NotAttended
      FROM Person p JOIN
           Episode e
           ON e.PersonID = p.PersonID
      WHERE e.Attended = 'No' AND
            e.EpisodeDate >= '2015-04-01' AND e.EpisodeDate <= '2016-03-31'
      GROUP BY p.PersonID
     ) p
GROUP BY NotAttended;
Comments