Solved

Percentages in SQL Server 2005

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

827 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