Solved

Calculate percentage

Posted on 2009-06-28
2
151 Views
Last Modified: 2012-05-07
I have a table with the columns Id(int), Units(int), ItemId(int), MyText(string)
I want to find all "Units" with itemId=2 and add them all together. Then I want to find how many percent the Units in each row is of the total number and return as "UnitsPercentage".  How do I do that?

Example:
Row1:  Id=1, Units=2, ItemId=2, MyText="Blue"
Row2:  Id=2, Units=4, ItemId=2, MyText="Green"
Row3:  Id=3, Units=3, ItemId=2, MyText="Yellow"
Row4:  Id=4, Units=1, ItemId=2, MyText="Red"

Return:
Id=1, UnitsPercentage="20"
Id=2, UnitsPercentage="40"
Id=3, UnitsPercentage="30"
Id=4, UnitsPercentage="10"
0
Comment
Question by:johnkainn
  • 2
2 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24731797
select distinct id, sum(units) over(partition by id) / count(*) over() as Unitspercentage
from myTable
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 24731798
select distinct id, 100*sum(units) over(partition by id) / count(*) over() as Unitspercentage
from myTable
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now