CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
Bogdan Pușcașu
Bogdan Pușcașu

Reputation: 575

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!

Upvotes: 0

Views: 58

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1271121

This is easy to do with window functions:

SELECT ClientCipher, SUM(LastWeeklyAccessCount) AS ClientAccessCount ,
       SUM(LastWeeklyAccessCount) / SUM(SUM(LastWeeklyAccessCount)) OVER () as Percentage
FROM SSLAccessClient 
WHERE ClientCipher IS NOT NULL 
GROUP BY ClientCipher;

This is the best way to write the query.

Note that SQL Server does integer division, so you might need to convert to numbers. So, it is possible that the expression you want is:

       SUM(LastWeeklyAccessCount) * 100.0 / SUM(SUM(LastWeeklyAccessCount)) OVER () as Percentage

Upvotes: 0

Daryl Wenman-Bateson
Daryl Wenman-Bateson

Reputation: 3964

You should be able to use an aggregate function for the total

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

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82010

Another option could be

;with cte as (
        SELECT  ClientCipher, SUM(LastWeeklyAccessCount) AS ClientAccessCount 
        FROM    SSLAccessClient 
        WHERE   ClientCipher IS NOT NULL 
        GROUP BY  ClientCipher
)
Select *
      ,Percentage = ClientAccessCount * 100.0 / (Select sum(ClientAccessCount) from cte)
 From  cte

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

SQL DEMO

SELECT 
       DISTINCT
       [ClientCipher],
       -- just for debug
       COUNT([ClientCipher]) over (partition by [ClientCipher]) num,
       COUNT([ClientCipher]) over () den,

       -- your percentage
       COUNT([ClientCipher]) over (partition by [ClientCipher]) *1.0 /
       COUNT([ClientCipher]) over () perc
FROM Table1

OUTPUT

enter image description here

Upvotes: 1

DVT
DVT

Reputation: 3127

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;

Upvotes: 1

Related Questions