Solved

Percentages in SQL Server 2005

Posted on 2014-12-22
3
93 Views
Last Modified: 2014-12-22
Hi,

I have a table with fields ProdGroup, ProdNo.
I would like to be able to select the Percentage of products that are in each group by specifying that group
Both fields are of datatype INT

Any help would be appreciated.
0
Comment
Question by:Morpheus7
  • 2
3 Comments
 
LVL 10

Expert Comment

by:Ray
ID: 40513205
you want to do this for everything in the table or only a specific ProdGroup or ProdGroups at a time?

Anything more you can share about the table structure (columns)?
0
 

Author Comment

by:Morpheus7
ID: 40513246
Hi,

I would like to be able to specify one or two prodGroups at a time. The other fields in the table are productID which is the PK. The others are just descriptive.
There are over one hundred prodGroups.
Thanks
0
 
LVL 10

Accepted Solution

by:
Ray earned 500 total points
ID: 40513308
This should do the trick.
Since I'm not sure if your table could have millions of rows or not, I opted for a 'faster' counting method for finding the total number of rows in the table.  Note that the % for each group will be the % of the total rows (prodNos), not the % of the limited group.  IF that is a problem, then there will need to be a change.


SELECT ProdGroup, cast(count(*) AS DECIMAL(18, 3)) / (
            SELECT SUM(row_count)
            FROM sys.dm_db_partition_stats
            WHERE object_id = OBJECT_ID('TABLENAME') AND (index_id = 0 OR index_id = 1)
            ) AS 'PercentageOfGroup'
FROM TABLENAME
WHERE ProdGroup in ('group1', 'group2', 'group3')
GROUP BY ProdGroup
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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

747 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