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

Posted on 2007-10-04
Last Modified: 2010-08-05
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  

Question by:Nikki28838
    LVL 50

    Accepted Solution

    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


    Author Comment

    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!!


    Author Comment

    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!


    Author Comment

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


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    734 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

    23 Experts available now in Live!

    Get 1:1 Help Now