Using SSMS 18.11.1

I have a table called email_archive in every row is a column called attachment. each row also has the column companyID

I am trying to get the percentage of entries per customerID that has nothing in the attachment column.

Any suggestions?

Table example data as requested

|id |CompanyID|  Originator |    MSGText    |Attachment |sendstatus|
| - | ------- | ----------- | --------------| --------- | -------- |
|1  |   HG1   |[email protected]|This is a test1| roses.txt |Delivered |
|2  |   HG3   |[email protected]|This is a test1| roses.txt |Failed    |
|3  |   HG4   |[email protected]|This is a test1| roses.txt |Failed    |
|4  |   HG5   |[email protected]|This is a test1|     null  |Delivered |
|5  |   HG1   |[email protected]|This is a test1| roses.txt |Failed    |
|6  |   HG1   |[email protected]|This is a test1| roses.txt |Delivered |    
|7  |   HG3   |[email protected]|This is a test1|     null  |Failed    |
|8  |   HG4   |[email protected]|This is a test1| roses.txt |Failed    |
|9  |   HG5   |[email protected]|This is a test1|     null  |Delivered |
|10 |   HG1   |[email protected]|This is a test1| roses.txt |Failed    |

sorry had above showing as a table in the preview but wont let me post as it thinks its code and needs to be indented

Looking for results such as

HG1 0%
HG2 0%
HG3 50%
HG4 0%
HG5 100%

Many thanks for any assistance in advance.

CodePudding user response：

Simply said you want to divide the number of rows without attachment by the number of total rows.

I am assuming here, that the rows without an attachment have an empty string and not NULL. The following two queries should give you the numbers you need.

SELECT COUNT(CompanyID) FROM email_archive WHERE attachment = ''

SELECT COUNT(CompanyID) FROM email_archive

If you want to do the calculation in SQL you would need to convert them into decimal first, because if you divide the two INT that you get from COUNT you will end up with an INT and not a decimal.

select convert(
        decimal(18, 0),
(
            SELECT COUNT(CompanyID)
            FROM email_archive
            WHERE attachment = ''
        )
    ) / convert(
        decimal(18, 0),
(
            SELECT COUNT(CompanyID)
            FROM email_archive
        )
    )

CodePudding user response：

I have joined two tables via CompanyID to get the count of those attachment that have null and those that are not null. I have then calculated the percentage and to the result I have concatenated '%' string.

select ea1.CompanyID,
       case when max(ea3.cnt) is not null then
                 CONCAT(round((max(cast(ea3.cnt as float))/count(*) *100), 2), '%')
            else
                 '0%'
            end prc
from email_archive ea1
left join (select count(*) cnt
                  , ea2.CompanyID  
           from email_archive ea2 
           where ea2.attachment is null
           group by ea2.CompanyID ) ea3
           on ea1.CompanyID = ea3.CompanyID
group by ea1.CompanyID

Here is a demo

CodePudding user response：

You can just use coonditional aggregation.

To avoid the int/decimal problem, multiply by 100.0

SELECT
  t.CompanyID,
  Pct = COUNT(CASE WHEN t.attachment IS NULL THEN 1 END) * 100.0 / COUNT(*)
FROM YourTable t
GROUP BY
  t.CompanyID;

db<>fiddle