Get Started Transforming Your Data in Snowflake

How to calculate percentage in SQL?

  • How-Tos FAQs
  • December 17, 2018

There are different ways to calculate percentage in SQL like 

  • Using OVER() clause
  • Using subquery
  • Using CTE

Calculating percentage in SQL

    Let us consider the ‘inventory’ table as below. We can calculate the percentage of each item in the inventory.

    CREATE TABLE inventory(
           item VARCHAR(50),
           avail_stock INT
    );
    
    INSERT INTO inventory
    SELECT 'laptop', 20 UNION ALL
    SELECT 'keyboard', 40 UNION ALL
    SELECT 'mouse', 70 UNION ALL
    SELECT 'speaker', 20 UNION ALL
    SELECT 'Monitor', 50;

    SQL SERVER

    -- Using OVER Clause
    SELECT item Item, avail_stock * 100.0/ SUM(avail_stock) OVER() 'Percentage(%)'
    FROM inventory
    
    -- Using CROSS APPLY
    SELECT item Item, avail_stock * 100.0/ sub.sum_avail_stock 'Percentage(%)'
    FROM inventory
    CROSS APPLY (SELECT SUM(avail_stock) sum_avail_stock FROM inventory) sub
    
    -- Using subquery in SELECT statement
    SELECT item Item, avail_stock * 100.0/ (SELECT SUM(avail_stock) FROM inventory) 'Percentage(%)'
    FROM inventory
    
    -- Using CTE
    ;WITH total AS
    (
          SELECT SUM(avail_stock) AS total
        FROM inventory
    )
    SELECT item Item,
        avail_stock * 100 / total.total AS 'Percentage(%)'
    FROM inventory
    CROSS JOIN total;
    
    -- Output of all above queries
    Item        Percentage(%)
    ----------------------------
    laptop      10.000000000000
    keyboard    20.000000000000
    mouse       35.000000000000
    speaker     10.000000000000
    Monitor     25.000000000000

    MySQL

    -- Using OVER Clause
    SELECT item Item, avail_stock * 100.0/ SUM(avail_stock) OVER() 'Percentage(%)'
    FROM inventory;
    
    -- Using Subquery in SELECT statement
    SELECT item Item, avail_stock * 100.0/ (SELECT SUM(avail_stock) FROM inventory) 'Percentage(%)'
    FROM inventory;
    
    -- Using CROSS JOIN
    SELECT item Item, avail_stock * 100/ sub.sum_avail_stock 'Percentage(%)'
    FROM inventory
    CROSS JOIN (SELECT SUM(avail_stock) sum_avail_stock FROM inventory) sub;
    
    -- Output of all above queries
    Item       Percentage(%)
    ----------------------------
    laptop     10.000000000000
    keyboard   20.000000000000
    mouse      35.000000000000
    speaker    10.000000000000
    Monitor    25.000000000000

    Up Next:

    Read In SQL, how to limit the number of rows after ordering it in Oracle DB?

    More Resources We Think You Might Like

    SQL FAQ Feat

    How to find the duplicate data in a table in SQL?

    Let us set up a ‘customers’ table with their email addresses: CREATE TABLE customers (     custom...

    • How-Tos FAQs
    • December 16, 2018
    SQL FAQ Feat

    In SQL, how to limit the number of rows after o...

    We can perform ‘Like’ operations in MongoDB using regular expressions. In fact, regular expressio...

    • How-Tos FAQs
    • December 16, 2018
    SQL FAQ Feat

    How to select the first row of each GROUP BY in...

    Let us create a table ‘orders’ to replicate the problem. CREATE TABLE orders (       ...

    • How-Tos FAQs
    • December 16, 2018