Jay Jay - 2 months ago 4
SQL Question

Combine Query with % in SQL

I have been trying to get the percentage of category(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. I am looking to get an exam breakdown of what percent of each category(concern) is being asked with a dynamic query.

Table A

SELECT exams_id as exams, Count(*) AS TotalQuestions
FROM exams_questions AS eq
JOIN concerns as con ON eq.concerns_id = con.concerns_id
GROUP BY exams_id
ORDER BY exams_id

+--------------+--------------+
| Exams |TotalQuestions|
+--------------+--------------+
| 1 | 200 |
| 2 | 100 |
| 3 | 400 |
| 4 | 150 |
+--------------+--------------+


Table B

select exams_id as exam, count(con.concerns_id) as numberOfConcern, con.concerns_description, sum(con.concerns_id) as countTotal
from exams_questions
join concerns as con on exams_questions.concerns_id = con.concerns_id
where exams_id is not null
group by exams_id, con.concerns_id, con.concerns_description
order by exams_id asc, con.concerns_id



+----------------+----------------+------------------+
| Exams |ConcernID | NumberofConcern |
+----------------+----------------+------------------+
| 1 | 1 | 25 |
| 1 | 5 | 37 |
| 1 | 33 | 24 |
| 1 | 43 | 35 |
| 1 | 44 | 7 |
| 1 | 45 | 22 |
| 1 | 46 | 27 |
| 1 | 47 | 33 |
| 2 | 1 | 20 |
| 2 | 4 | 25 |
| 2 | 22 | 35 |
| 2 | 24 | 20 |
+----------------+----------------+------------------+


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

+-------------------+-----------------+-------------------+---------------+
| Exam | ConcernID | NumberofConcern | ExamPercent |
+-------------------+-----------------+-------------------+---------------+
| 1 | 1 | 25 | .24 |
| 1 | 5 | 27 | .26 |
| 1 | 33 | 24 | .23 |
| 1 | 43 | 35 | .33 |
| 1 | 44 | 7 | .07 |
| 1 | 45 | 22 | .21 |
| 1 | 46 | 27 | .26 |
| 1 | 47 | 33 | .31 |
| 2 | 1 | 20 | .2 |
| 2 | 4 | 25 | .25 |
| 2 | 22 | 35 | .35 |
| 2 | 24 | 20 | .2 |
+-------------------+-----------------+-------------------+---------------+


This works great if there are 100 questions like exam 2. However the number of questions is static and needs to change with the exam and incorporate the TotalQuestions.

Cast(count(*)* 100.0 / sum(count(*)) over() AS DECIMAL(18, 2)) as ExamPercent


from the combine table I believe is where the change needs to be at.

Thanks for any guidance

Answer

Isn't that you are looking for ?

Select EQ.exams_id as exam,  count(con.concerns_id) as numberOfConcern, con.concerns_description, sum(con.concerns_id) as countTotal, tot.TotalQuestions, 
       ROUND(CAST(( count(con.concerns_id) * 100.0 / tot.TotalQuestions) AS FLOAT), 2) AS ExamPercent 
from exams_questions EQ
join concerns as con on exams_questions.concerns_id = con.concerns_id
Join (
            SELECT exams_id as exams, Count(*) AS TotalQuestions
            FROM exams_questions AS eq 
            JOIN concerns as con ON eq.concerns_id = con.concerns_id
            GROUP BY exams_id
     ) tot ON EQ.exams_id = tot.exams_id
where EQ.exams_id is not null 
group by EQ.exams_id, con.concerns_id, con.concerns_description
order by EQ.exams_id asc, con.concerns_id 

Your first query compute the totals per exam_id. I have thus joined it to your second query to have the totals for each exam on the same row, and then computed the percentage with this total.

Comments