AVG of 3 SELECT statements with GROUP BY

Question

I am trying to do the following

I have a QA table that contains the following:

TICKET_ID SKILL_ID SCORE USER ############################################### 1 10 15 USER1 1 20 5 USER1 1 30 95 USER1 2 40 20 USER1 2 50 40 USER1 3 60 70 USER1 3 70 15 USER1 

Skills table:

 SKILL_ID SKILL_NAME AREA_ID 10 SKILL1 1 20 SKILL2 1 30 SKILL3 2 40 SKILL4 2 50 SKILL5 2 60 SKILL6 3 70 SKILL7 3 

and TICKET tables made of:

  TICKET_ID TICKET_NUMBER 1 AAA 2 BBB 3 CCC 

QA has FK for TICKETS using TICKET_ID, as well as FK for SKILLS using SKILL_ID

What do I need to do:

for each ticket in the QA table, check that the total score in each area does not exceed 100, and if the value 0 is exceeded, then make an AVG of 3 results and groups by the ticket number, which is in another table. Therefore, for each case I will need to calculate the points for each area, if> 100, then 0 still leave the original value, then make an average of three values ​​and a group by ticket number

Not sure if this is possible

Therefore, perhaps to calculate the area_1 score, I do the following:

 SELECT DECODE(100 - SUM(SCORE), 100,100, 95,95, 90,90, 85,85, 80,80, 75,75, 70,70, 65,65, 60,60, 55,55, 50,50, 45,45, 40,40, 35,35, 30,30, 25,25, 20,20, 15,15, 10,10, 5,5, 0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 1) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1 

to calculate the area_2 batch, but changing the WHERE clause (WHERE AREA_ID = 2 this time):

 SELECT DECODE(100 - SUM(SCORE), 100,100, 95,95, 90,90, 85,85, 80,80, 75,75, 70,70, 65,65, 60,60, 55,55, 50,50, 45,45, 40,40, 35,35, 30,30, 25,25, 20,20, 15,15, 10,10, 5,5, 0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 2) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1 

and compute the estimate of region_3, but by changing the WHERE clause (WHERE AREA_ID = 3):

 SELECT DECODE(100 - SUM(SCORE), 100,100, 95,95, 90,90, 85,85, 80,80, 75,75, 70,70, 65,65, 60,60, 55,55, 50,50, 45,45, 40,40, 35,35, 30,30, 25,25, 20,20, 15,15, 10,10, 5,5, 0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 3) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1 

Each of these blocks gives 1 value as an output.

What I'm trying to achieve is to deduce AVG from 3 blocks using ticket_number

I tried to sum 3 blocks together, but this does not allow me:

 SELECT DECODE(100 - SUM(SCORE), 100,100, 95,95, 90,90, 85,85, 80,80, 75,75, 70,70, 65,65, 60,60, 55,55, 50,50, 45,45, 40,40, 35,35, 30,30, 25,25, 20,20, 15,15, 10,10, 5,5, 0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 1) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1 + SELECT DECODE(100 - SUM(SCORE), 100,100, 95,95, 90,90, 85,85, 80,80, 75,75, 70,70, 65,65, 60,60, 55,55, 50,50, 45,45, 40,40, 35,35, 30,30, 25,25, 20,20, 15,15, 10,10, 5,5, 0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 2) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1 + SELECT DECODE(100 - SUM(SCORE), 100,100, 95,95, 90,90, 85,85, 80,80, 75,75, 70,70, 65,65, 60,60, 55,55, 50,50, 45,45, 40,40, 35,35, 30,30, 25,25, 20,20, 15,15, 10,10, 5,5, 0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 3) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1 

thanks

Answer 1

Try the following query:

 SELECT AREAS_SCORES.USER, AREAS_SCORES.TICKET_ID, AREAS_SCORES.TICKET_NUMBER, AVG(CASE WHEN AREAS_SCORES.AREA_SUM_SCORE > 100 THEN 0 ELSE AREAS_SCORES.AREA_SUM_SCORE END) AVG_SCORE FROM ( SELECT QA.USER, QA.TICKET_ID, QA.TICKET_NUMBER, SKILLS.AREA_ID, SUM(QA.SCORE) AREA_SUM_SCORE FROM QA INNER JOIN SKILLS ON SKILLS.SKILL_ID = QA.SKILL_ID INNER JOIN TICKETS ON TICKETS.TICKET_ID = QA.TICKET_ID GROUP BY QA.USER, QA.TICKET_ID, QA.TICKET_NUMBER, SKILLS.AREA_ID ) AREAS_SCORES 

The subquery calculates the sum of the amount in each area for each ticket of each user. Then the information is aggregated again to calculate the average value, but with the restriction that when the total score for a certain area exceeds 100 , then it should be considered 0 .

I hope this helps in some way (assuming I understand your problem well).

Answer 2

You can do all this with one choice. You will need to combine SUM with CASE , for example:

 SELECT T.TICKET_NUMBER, SUM(CASE WHEN AREA_ID = 1 THEN QA.SCORE ELSE 0 END) SCORE_A1, SUM(CASE WHEN AREA_ID = 2 THEN QA.SCORE ELSE 0 END) SCORE_A2, SUM(CASE WHEN AREA_ID = 3 THEN QA.SCORE ELSE 0 END) SCORE_A3 FROM QA INNER JOIN SKILLS S ON QA.SKILL_ID = S.SKILL_ID INNER JOIN TICKETS T ON QA.TICKET_ID = T.TICKET_ID WHERE QA.USER = :P2_USER GROUP BY T.TICKET_NUMBER 

Now, to apply additional criteria to ensure that the SUM does not exceed 100, use an external query:

 SELECT TICKET_NUMBER, CASE WHEN SCORE_A1 > 100 THEN 0 ELSE SCORE_A1 END SCORE_A1, CASE WHEN SCORE_A2 > 100 THEN 0 ELSE SCORE_A2 END SCORE_A2, CASE WHEN SCORE_A3 > 100 THEN 0 ELSE SCORE_A3 END SCORE_A3 FROM ( SELECT T.TICKET_NUMBER, SUM(CASE WHEN AREA_ID = 1 THEN QA.SCORE ELSE 0 END) SCORE_A1, SUM(CASE WHEN AREA_ID = 2 THEN QA.SCORE ELSE 0 END) SCORE_A2, SUM(CASE WHEN AREA_ID = 3 THEN QA.SCORE ELSE 0 END) SCORE_A3 FROM QA INNER JOIN SKILLS S ON QA.SKILL_ID = S.SKILL_ID INNER JOIN TICKETS T ON QA.TICKET_ID = T.TICKET_ID WHERE QA.USER = :P2_USER GROUP BY T.TICKET_NUMBER ) TIX 

And finally, if you need the average of these three points, use a different level of external queries:

 SELECT TICKET_NUMBER, SCORE_A1, SCORE_A2, SCORE_A3, (SCORE_A1+SCORE_A2+SCORE_A3)/3 AVG_SCORE FROM ( SELECT TICKET_NUMBER, CASE WHEN SCORE_A1 > 100 THEN 0 ELSE SCORE_A1 END SCORE_A1, CASE WHEN SCORE_A2 > 100 THEN 0 ELSE SCORE_A2 END SCORE_A2, CASE WHEN SCORE_A3 > 100 THEN 0 ELSE SCORE_A3 END SCORE_A3 FROM ( SELECT T.TICKET_NUMBER, SUM(CASE WHEN AREA_ID = 1 THEN QA.SCORE ELSE 0 END) SCORE_A1, SUM(CASE WHEN AREA_ID = 2 THEN QA.SCORE ELSE 0 END) SCORE_A2, SUM(CASE WHEN AREA_ID = 3 THEN QA.SCORE ELSE 0 END) SCORE_A3 FROM QA INNER JOIN SKILLS S ON QA.SKILL_ID = S.SKILL_ID INNER JOIN TICKETS T ON QA.TICKET_ID = T.TICKET_ID WHERE QA.USER = :P2_USER GROUP BY T.TICKET_NUMBER ) TIX ) MORE_TIX