Jay Jay - 1 year ago 57
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download