Basic Custom Metric Creation

Get Help
1

Hi !

I am trying to create a “Progress” measure with custom SQL, basically a percentage completion. I am so close but cant get the final piece to the puzzle as it just says Zero when in my mind it should say the percentage completion? I am using multiple CROSS JOINS…

SELECT a.count1 / (a.count1 + b.count2 + c.count3) FROM
(SELECT count(*) AS "count1"
FROM "stitch_jira"."issues"
WHERE "stitch_jira"."issues"."fields__status__statusCategory__name" = 'To Do') a
CROSS JOIN 
(SELECT count(*) AS "count2"
FROM "stitch_jira"."issues"
WHERE ("stitch_jira"."issues"."fields__status__statusCategory__name" = 'In Progress')) b  
CROSS JOIN 
(SELECT count(*) AS "count3"
FROM "stitch_jira"."issues"
WHERE ("stitch_jira"."issues"."fields__status__statusCategory__name" = 'Done')) c

Any ideas? What am I doing wrong here?

2

Hi @ChenDogg
If you want to do a percentage (out of hundred), then you would probably want to do something like this:

SELECT 100 / NULLIF(a.count1, 0) * (b.count2 + c.count3) FROM
...

The NULLIF() protects against divide-by-zero, if a.count1 is zero. (I’m guessing you’re using Postgres)

3

I solved it like so:

SELECT
1.0*(COUNT() FILTER(WHERE fields__status__statusCategory__name=‘To Do’))/
(COUNT() FILTER(WHERE fields__status__statusCategory__name
IN(‘To Do’,‘In Progress’,‘Done’)))
FROM “stitch_jira”.“issues”