Bogdan Pușcașu Bogdan Pușcașu - 11 days ago 10
SQL Question

SQL - How to compute percentage and add to query result

After using this query:

SELECT
ClientCipher, SUM(LastWeeklyAccessCount) AS ClientAccessCount
FROM
SSLAccessClient
WHERE
ClientCipher IS NOT NULL
GROUP BY
ClientCipher


This is the result:

|---------------------|------------------|
| ClientCipher | AccessCount |
|---------------------|------------------|
| TLS 1.0 | 2 |
| TLS 1.1 | 2 |
| TLS 1.2 | 4 |
|---------------------|------------------|


How can I append to this result the percentage of clients using TLS 1.0, TLS 1.1, etc.? Something like this:

|---------------------|------------------|------------------|
| ClientCipher | AccessCount | Percentage |
|---------------------|------------------|------------------|
| TLS 1.0 | 2 | 25% |
| TLS 1.1 | 2 | 25% |
| TLS 1.2 | 4 | 50% |
|---------------------|------------------|------------------|


Any help is appreciated! Thanks!

DVT DVT
Answer

This way do not use Window function (probably work for older version).

SELECT
    a. ClientCipher
    , 100.00 * a.ClientAccessCount / b.TotalSum AS Percentage
FROM
    (
        SELECT 
            ClientCipher, SUM(LastWeeklyAccessCount) AS ClientAccessCount 
        FROM 
            SSLAccessClient 
        WHERE 
            ClientCipher IS NOT NULL 
        GROUP BY 
            ClientCipher
    ) a
    CROSS JOIN
    (
        SELECT SUM(LastWeeklyAccessCount) AS TotalSUM
        FROM
            SSLAccessClient
        WHERE
            ClientCipher IS NOT NULL
    ) b;