Select Distinct query

Question

I want to retrieve data in the format:

Category, Question, Status, Answer

I dont want any repetition so I retrieved unique Category, Question & Status by:

select distinct category, question, status
from sharepoint_vw

However I want to have an additional column as 'Answer' to be a % value calculated by the expression:

=SUM(IIF(Fields!Status.Value="Unfavourable",1,0))/COUNT(Fields!Status.Value)

I just dont know how to convert this expression into SQL and combine it with the select distinct query to get the results I need.

Answer 1

something like:

select category, question
avg(case status when 'unfavorable' then 1.0 else 0.0 end) as avgunfavorable
from sharepoint_vw
group by  category, question

Answer 2

If you really want Status as part of the output from the SQL, then you will have to aggregate your total count for each category, question in a subquery. Then you can use that total in your calculations in your main query:

SELECT DISTINCT
  sharepoint_vw.category,
  sharepoint_vw.question,
  sharepoint_vw.status,
  count(*)/total.rcount as answer
FROM sharepoint_vw
  LEFT OUTER JOIN 
    (
       SELECT 
         category, 
         question,          
         CAST(COUNT(*) AS DECIMAL(10,2)) as rcount 
       FROM sharepoint_vw 
       GROUP BY category, question
     ) as total
       ON sharepoint_vw.category = total.category
         AND sharepoint_vw.question = total.question
GROUP BY 
  sharepoint_vw.category,
  sharepoint_vw.question,
  sharepoint_vw.status,
  total.rcount

That answer columns will hold the percentage total for each status now. You can see this in action at this sqlfiddle

Something worth mentioning is that we are dealing with integers here (sums of counts) So we will have to cast at least one of these to a Decimal() type otherwise you'll just get an integer return.