Problems calculating percentages using sub-queries

[TomW]

I am trying to calculate a metric to show the average percentage of incidents associated with changes by dividing 'the number of incidents linked to changes' by 'the total number of changes' and multiplying the result by 100. I would like to show this in a widget and using a count list I am able to calculate the following.

Number of incidents linked to changes:

"SELECT
 COUNT h_sm_relationship_entities.h_entity_id
FROM
 h_itsm_requests,h_sm_relationship_entities
WHERE
 (h_itsm_requests.h_pk_reference = h_sm_relationship_entities.h_linked_entity_id)
 AND
 (h_sm_relationship_entities.h_linked_entity_id LIKE 'IN%')
 AND
 (h_sm_relationship_entities.h_entity_id LIKE 'CH%')"

Total number of changes:

"SELECT
 COUNT h_itsm_requests.h_pk_reference
FROM
 h_itsm_requests
WHERE
 h_itsm_requests.h_pk_reference LIKE 'CH%'"

But the metric for this is difficult. I currently have the following but the system doesn't like it:

"(
(SELECT
 COUNT h_sm_relationship_entities.h_entity_id
FROM
 h_itsm_requests,h_sm_relationship_entities
WHERE
 (h_itsm_requests.h_pk_reference = h_sm_relationship_entities.h_linked_entity_id)
 AND
 (h_sm_relationship_entities.h_linked_entity_id LIKE 'IN%')
 AND
 (h_sm_relationship_entities.h_entity_id LIKE 'CH%'))
DIV
(SELECT
 COUNT h_itsm_requests.h_pk_reference
FROM
 h_itsm_requests
WHERE
 h_itsm_requests.h_pk_reference LIKE 'CH%')
)
* 100"

If anyone could offer some advice, or point me in the right direction if I am barking up the wrong tree, then please let me know.

Cheers
Tom

[ArmandoDM]

@TomW

can you try this one ?

 

SELECT (
(SELECT COUNT(h_sm_relationship_entities.h_entity_id)
FROM
h_itsm_requests,h_sm_relationship_entities
WHERE
(h_itsm_requests.h_pk_reference = h_sm_relationship_entities.h_linked_entity_id)
AND
(h_sm_relationship_entities.h_linked_entity_id LIKE 'IN%')
AND
(h_sm_relationship_entities.h_entity_id LIKE 'CH%'))
/
(SELECT COUNT(h_itsm_requests.h_pk_reference)
FROM
h_itsm_requests
WHERE
h_itsm_requests.h_pk_reference LIKE 'CH%')
)
* 100

 

Regards

 

Armando

[TomW]

Thanks for your recommendation Armando, when I tried it I ended up getting a MariaDB error message about incorrect syntax appearing where the counter would be showing. I have adapted what you recommended and have managed to get it working. To do this I selected 'AVG' from the drop down list under 'SELECT' (COUNT wouldn't work as it counts rows from a list where as AVG works on numbers).

I put 'h_itsm_requests,h_sm_relationship_entities' in the text box under 'FROM'

In the @WHERE' box I typed 'h_itsm_requests.h_pk_reference = h_sm_relationship_entities.h_linked_entity_id' (I'm not sure this actually does anything)

In the little text box between the 'SELECT' and 'DATA FORMAT' drop downs I pasted in:

Quote

 

((SELECT COUNT(h_sm_relationship_entities.h_entity_id) FROM h_itsm_requests,h_sm_relationship_entities WHERE (h_itsm_requests.h_pk_reference = h_sm_relationship_entities.h_linked_entity_id) AND (h_sm_relationship_entities.h_linked_entity_id LIKE 'IN%') AND (h_sm_relationship_entities.h_entity_id LIKE 'CH%')) / (SELECT COUNT(h_itsm_requests.h_pk_reference) FROM h_itsm_requests WHERE h_itsm_requests.h_pk_reference LIKE 'CH%') ) * 100

 

Finally after much palava it works.

I have adapted the above for releases too by changing the 'CH%' wording, after the 'LIKE' statements, to 'RM%'.

My brain is now numb!

[ArmandoDM]

Hi @TomW, the query was working for me so I suggested it.

Good work anyway, glad to hear that you got it working.