I have been trying to get a percentage of concerns from each test however I am having trouble combining the two queries. Where I am having trouble is the divide by and getting the correct query. I used How to calculate percentage with a SQL statement for reference.
Table A
SELECT exams_id as exams, Count(*) AS TotalExamQuestions
FROM exams_questions AS eq
JOIN concerns as con ON eq.concerns_id = con.concerns_id
GROUP BY exams_id
ORDER BY exams_id
Table B
SELECT e.exams_description, eq.exams_id as exams, con.concerns_id as ConcernID, count(*) as NumberOfConcerns
FROM exams_questions as eq
JOIN concerns as con ON eq.concerns_id = con.concerns_id
JOIN exams as e ON e.exams_id = eq.exams_id
WHERE eq.exams_id is not null
GROUP BY eq.exams_id, con.concerns_id, con.concerns_description, e.exams_description
ORDER BY eq.exams_id asc, con.concerns_id
Combine Table:
SELECT e.exams_description, eq.exams_id as exams, con.concerns_id as ConcernID, Count(*) as numberofQuestions, Cast(Count(*)* 100.0 / Sum(Count(*)) OVER() AS DECIMAL(18, 2)) as ExamPercent
FROM exams_questions as eq
JOIN concerns AS con on eq.concerns_id = con.concerns_id
JOIN exams AS e on e.exams_id = eq.exams_id
GROUP BY eq.exams_id, con.concerns_id, e.exams_description
ORDER BY eq.exams_id asc, con.concerns_id
This works great if there is 100 exam questions. However the exams vary in length. I believe the line to change is:
Cast(count(*)* 100.0 / sum(count(*)) over() AS DECIMAL(18, 2)) as ExamPercent
How would I go about making this dynamic to the number of questions from table A?
Thanks for any guidance.
