CODEWITHSUNDEEP
c#sqlstatistics
Rod
Rod

Reputation: 15475

SQL Query: For each value, determine the percentage of rows that contain the value?

Let's say I have a contact manager system. There are notes associated to each contact made by employees.

So, here's my quick example:

ContactName, NoteCount
John, 100
Rob, 10 
Amy, 10
Chris, 10

How do i figure out the that 75% of contacts have 10 notes assoicated with them and that 25% of contacts have 100 notes associated with them?

Please explain what I'm trying to do in Layman's terms.

Upvotes: 0

Views: 108

Answers (2)

remi bourgarel
remi bourgarel

Reputation: 9389

select 
((countTen/countTotal)*100) as percentTen,
((countHundred/countTotal)*100) as percentHundred
FROM (
  select 
  cast(sum(case when noteCount <= 10 then 1 else 0 end) as float) as countTen,
  cast(sum(case when noteCount <= 100 and > 10 then 1 else 0 end) as float) as countHundred,
  cast(count(*) as float) as countTotal
  from
  contacts
) temp

Should be ok, I often use the trick sum + case when i need to do a count on a filter

Upvotes: 1

Samuel Neff
Samuel Neff

Reputation: 74939

If you really want the percentage of people that have the exact number, use this:

SELECT
    NoteCount,
    COUNT(*) ContactsWithThisNoteCount,
    COUNT(*) / (SELECT COUNT(*) FROM Contacts) PercentageContactsWithThisNoteCount
FROM 
    Contacts
GROUP BY
    NoteCount

If you want grouings like "0-9", "10-99", and "100+" then you just need a little bit of a calculation in the group by and MIN/MAXon NoteCount.

Upvotes: 4

Related Questions