# 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``````

### Transform Data in Snowflake Today!

The only multi-persona SaaS solution for data transformation in Snowflake.

## More Resources We Think You Might Like

### 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

### 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

### 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