Combine Query with % in SQL

Question

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

I am still trying to decipher what you are trying to ask. If Exam 1 has 200 questions, you have 8 Exam 1 records in your database. Are you trying to see what percentage 8 is of 200? — M T Head, Sep 22 '16 at 19:44

score 0 · Accepted Answer · answered Sep 22 '16 at 19:41

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.

Combine Query with % in SQL

1 Answers1