How to calculate percentage in SQL on Snowflake
- How-Tos FAQs
- December 17, 2018
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 inventoryKeep 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.000000000000MySQL
-- 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.000000000000Up Next:
Read In SQL, how to limit the number of rows after ordering it in Oracle DB?
