CODEWITHSUNDEEP
sqlgroup-by
Aurel
Aurel

Reputation: 21

SQL percent calculation with group in the original table

I have a problem using SQL :

I have a table :

School Name  |  Class |   Studends  
===================================  
Golden       |  1     |   20  
Red          |  1     |   80  
Golden       |  2     |   7  
Red          |  2     |   14  
Golden       |  3     |   3  
Red          |  3     |   1

And I want to have the following results :

School Name  |  Class |   Percent     
======================================  
Golden       |  1     |   20 %  
Red          |  1     |   80 %  
Golden       |  2     |   33 %  
Red          |  2     |   66 %  
Golden       |  3     |   75 %  
Red          |  3     |   25 %

I try to find the solution but didn't find, anyone have the answer ?

Upvotes: 2

Views: 154

Answers (2)

NiematojakTomasz
NiematojakTomasz

Reputation: 2473

SELECT 
    a."School Name",
    a.Class,
    a.Studends / SUM(b.Students)
FROM YourTable a 
LEFT JOIN YourTable b ON a.Class = b.Class 
GROUP BY 
    a."School Name",
    a.Class,
    a.Studends,
    b.Class

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453909

If your RDBMS supports windowed aggregates

SELECT 
    School_Name,
    Class,
    100.0 * Studends / SUM(Studends) OVER (PARTITION BY Class ) AS Percent
FROM YourTable

Upvotes: 1

Related Questions