Solved

Percentages in SQL Server 2005

Posted on 2014-12-22
3
95 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 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

14 Experts available now in Live!

Get 1:1 Help Now