Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Percentages in SQL Server 2005

Posted on 2014-12-22
3
Medium Priority
?
104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 11

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 11

Accepted Solution

by:
Ray earned 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

636 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