[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-10-04
Medium Priority
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
  • 3
LVL 50

Accepted Solution

Lowfatspread earned 200 total points
ID: 20016966
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

ID: 20017192
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

ID: 20017507
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

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


Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

831 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