CODEWITHSUNDEEP
mysqlsql
al_sweets
al_sweets

Reputation: 136

Simple Percentage Column in SQL

I am relatively new to SQL and looking to pick up a few simple tricks. I have managed to create a query that selects each different type of car permit (chargeType), counts the number issued for each one (num), and adds a column that shows the total number of permits issued (total). The code is below.

SELECT chargeType, 
       COUNT(chargeType) AS num,
       (SELECT COUNT(chargeType) 
       FROM permit) AS total
FROM permit
GROUP BY chargeType

I now want to add a final column which shows the percentage of each permit type issued. So the number of each permit type divided by the total multiplied by 100, but I am struggling to do it. Can anybody help?

Upvotes: 0

Views: 69

Answers (2)

Peter
Peter

Reputation: 16943

This will work. The plus of this solution is there is no subquery in SELECT

SELECT *, (num * 100 / total) as percentage
FROM 
(
SELECT
 chargeType, 
 COUNT(chargeType) AS num,
 total,
 (num * 100 / total) as percentage
FROM
  (SELECT COUNT(chargeType) as total FROM permit) ttotal
CROSS JOIN
  permit
GROUP BY
 chargeType
) tsub

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

Try something like this 

SELECT chargeType,
       num,
       total,
       num / NULLIF(total, 0) * 100 AS Percenatge
FROM   (SELECT chargeType,
               Count(chargeType) AS num,
               (SELECT Count(chargeType)
                FROM   permit)   AS total
        FROM   permit
        GROUP  BY chargeType) a

NULLIF is used to avoid divide by zero expection

Upvotes: 1

Related Questions