Issue with using two expressions to create another expression in SQL server 2005

Hello.  I am brand new to SQL server databases so please bear with me.  I am using Visual Studio to create a report. I created my dataset and the report is working properly excluding the following...
 The report contains two groups, Category and Subcategory.  Both totals are based on the sum of "ContactTotal" field in my dataset.  My issue is that I need to divide the number of each subcategory by the overall Category total.  For example,  the percentage of 'converter issue' would be 50% (50/100).  The others would be 25%, however, I cannot create the expression being as both totals are based on the sum of the same field name.  I guess in easier way to approach this issue would be to find out if there is a way I can re-name the calculated fields (ie CategoryTotal, SubCategoryTotal) so visual studio will recognize that I am trying to divide the Subcategory Total by the Category Total.  Any assistance would be tremendously appreciated!  

Category        Total              Subcategory            Total          % of contacts  
Tech Issue     100
                                             Converter Issue       50                  
                                             No Dial Tone             25
                                             Modem Offline          25  

Who is Participating?
LowfatspreadConnect With a Mentor Commented:
form your dataset from

Select st.Category,st.subcategory,sctot,ctot,case when ctot =0 then 0
       else (sctot * 100.000)/ctot end
  from (
select category,subcategory,sum(contacttotal) as SCtot
 from yourtable
 group by category,subcategory
   ) as ST
 Inner Join (select category,sum(contacttotal) as Ctot
                    from yourtable
                    group by category
                  ) as CT
 on ST.Category=CT.Category
order by 1,2

do you need to handle positive and negative contributions to  the totals differently?

e.g.  cat total 3  
   subcat1   4      percent 1.3333
  subcat2 - 1       percent -0.3333

Nikki28838Author Commented:
That works perfectly!!  I went about it a completely different way, however, reading the code you sent, it does make sense to me.  Thank you so much for your prompt response!!

Nikki28838Author Commented:
Hello...again!  I have been playing with the code you sent, and again, it works perfectly.  I was then trying to enter parameters (ie customer location) to the dataset but can't figure it out.  I know, down the road, I will be asked for a report separated by the customer location.  I have been playing with this for a while and cannot figure it out.  An example would be to pull from the same table "WHERE Site = '9'" and implementing the dataset you suggested.  

Also, would you please explain the use of "st." before the fields names that you are using to group the data? Thank you so much for your patience!

Nikki28838Author Commented:
Good may disregard my previous entry...I figured it out.  Again, thank you so much!!!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.