SQL - Return a Sum of Counts-CodePudding

My data represents a 1 to many relationship between applicants and documents attached to each applicant.

I have a query that will return how many documents are attached to each application:

Select a.ApplicationId, count(att.AttachmentId) as _Count from [db].Application a
inner join [db].Attachment att
on att.ApplicationId=a.ApplicationId
group by a.applicationid, att.ApplicationId

That returns data like this:

ApplicationId    _Count
_____________    ______
1                   3
2                   1
3                   9
4                   6
5                   3

What I would like is a summary how many cases have 1 document, how many have 2 documents, how many have 9 documents, etc.. Like this:

DocCount             Total
_______              _____
1                      42
2                      33
3                      29
4                      51

CodePudding user response：

You can get this value by applying an outer query to your current query. Just use the _Count value as the group expression in the outer query.

select  _Count As DocCount, count(*) As Total
from
(
    Select a.ApplicationId, count(att.AttachmentId) as _Count from [db].Application a
    inner join [db].Attachment att
    on att.ApplicationId=a.ApplicationId
    group by a.applicationid, att.ApplicationId
)as X
group by
    _Count
order by
   _Count

CodePudding user response：

IF the RDBMS is SQL SERVER then:

WITH ApplicationCounts AS (
SELECT a.ApplicationId, COUNT(att.AttachmentId) AS _Count
FROM db.Application a
     INNER JOIN db.Attachment att ON att.ApplicationId = a.ApplicationId
GROUP BY a.applicationid, att.ApplicationId
)
SELECT _Count, COUNT(*) FROM ApplicationCounts
GROUP BY _Count