CODEWITHSUNDEEP
sqlsql-server
Dave
Dave

Reputation: 127

Percentage of the same column in SQL

I need to calculate the percentage of total in SQL I've got

customerID, quantity

What i have tried is: 

SELECT 
   customer_id, 
   sum(quantity) / (quantity * 100)
FROM MyTable
Group by customer_id

Upvotes: 3

Views: 1001

Answers (6)

Anuj Tiwari
Anuj Tiwari

Reputation: 1

DECLARE @TotalQty int
SET @TotalQty = (SELECT SUM(Amount) FROM tbltemp)    
SELECT id, Amount * 100 / @TotalQty  FROM tbltemp

Upvotes: 0

Adil
Adil

Reputation: 148110

You need to get total quantity first to calculate the customer quantity percentage.

DECLARE @TotalQty int
set @TotalQty = (select sum(Amount) FROM tbltemp)    
SELECT id, Amount * 100 / @TotalQty  FROM tbltemp

Upvotes: 1

Christian Barron
Christian Barron

Reputation: 2755

Just use a sub select:

Select customer_id, quantity, quantity / sum(quantity) * 100 per
From
(SELECT customer_id, 
   sum(quantity) quantity
FROM MyTable
Group by customer_id) a

Upvotes: 1

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

Reputation: 93694

Most efficient way would be using sum () over. Try this.

SELECT 
   customer_id, 
   (quantity * 100)/sum(quantity) over() 
FROM MyTable

Upvotes: 1

SMA
SMA

Reputation: 37023

Try something like fiddle

SELECT customer_id, (sum(quantity) / count(quantity ) * 100)
FROM MyTable 
Group by customer_id

Upvotes: 1

Bohemian
Bohemian

Reputation: 424983

You need the total to calculate the percentage, so use a sub query. To avoid calculating the total over again for every user, cross join to the total calculated once:

select customer_id, quantity * 100 / total
from MyTable 
cross join (
    select sum(quantity) total
    from MyTable) x

Upvotes: 3

Related Questions