• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

Percentages in SQL Server 2005

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
Morpheus7
Asked:
Morpheus7
  • 2
1 Solution
 
RayData AnalystCommented:
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
 
Morpheus7Author Commented:
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
 
RayData AnalystCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now