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.

It seems like you are mixing SQL Server and Reporting Services — Lamak
– Lamak, Commented Aug 23, 2018 at 13:35

user1443098 · Accepted Answer · 2018-08-23 14:01:18Z

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

edited Aug 23, 2018 at 14:01

answered Aug 23, 2018 at 13:37

user1443098

7,76511 gold badges43 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oshikuru Over a year ago

Thanks but avgunfavorable is not coming out with decimals like I expected, they are all coming out as 1?

user1443098 Over a year ago

I changed the query. check it out

JNevill · Accepted Answer · 2018-08-23 13:56:17Z

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.

Sorry I think this does work however I forgot to mention theres a Year column in the database so the count should be only where Year = '2018', can you advise please thanks a lot
Just add a `WHERE Year = '2018' before each GROUP BY (in the subquery and main query).
Thanks just did it all working perfectly. Thanks so much for the help

Collectives™ on Stack Overflow

Select Distinct query

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related