How to calculate percentage in SQL on Snowflake

There are different ways to calculate percentage in SQL like:

• Using Ratio_to_report() function or the Percent_Rank function
• 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;

Snowflake SQL

In Snowflake, you can use the Ratio_to_report() function or the Percent_Rank function, depending on your use case.

RATIO_TO_REPORT()
select
    item,
    stock,
    ratio_to_report(stock) over () as overall_stock_pct
from inventory

Keep Tabs on your “Calculated(%)” SQL Using Datameer’s Graphical Interface

Datameer is a collaborative, multi-persona data transformation platform that integrates with Snowflake.

With Datameer on Snowflake, you can use the SQL functions you’re familiar with to calculate your percentage and visually track all your calculated percentage queries with our low-code GUI interface.

To reap the benefits of these easy drag-and-drop modeling and self-documenting features, kickstart your Snowflake instance and connect your Datameer account.

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?