Adding 'group by' to JIRA sql changes results

[Kuzko]

Hi All,

Brand new to both Jira and SQL so please bear with me!

I'm using SQL to select the time worked on certain activities by employees. The base query works fine (validated via other means). However, when I add a simple 'group by' to the query, only a fraction of the data is returned compared to the original.

---

Base query used:

select
   wl.author,
   truncate(wl.timeworked/3600, 2) as 'Time (Hrs)'
from
   jiraissue ji
   join customfieldvalue cfv on cfv.ISSUE = ji.ID
   join customfield cf on cf.ID = cfv.CUSTOMFIELD
   join customfieldoption cfo on cfo.CUSTOMFIELD = cf.ID and cfo.ID = cfv.STRINGVALUE         join worklog wl on wl.issueid = ji.ID
   join project p on p.ID = ji.PROJECT
where
   cf.cfname = 'Bill work to Customer' and
   cfo.customvalue <> 'Internal' and
   (wl.startdate >= '2023-07-01 07:00:00') and (wl.startdate < '2023-10-01 07:00:00')

This returns 4630 rows, equating to 93 unique users and a total time of 6436.78 hrs.

---

Modified query (just adding group by):

select
   wl.author,
   truncate(wl.timeworked/3600, 2) as 'Time (Hrs)'
from
   jiraissue ji
   join customfieldvalue cfv on cfv.ISSUE = ji.ID
   join customfield cf on cf.ID = cfv.CUSTOMFIELD
   join customfieldoption cfo on cfo.CUSTOMFIELD = cf.ID and cfo.ID = cfv.STRINGVALUE         join worklog wl on wl.issueid = ji.ID
   join project p on p.ID = ji.PROJECT
where
   cf.cfname = 'Bill work to Customer' and
   cfo.customvalue <> 'Internal' and
   (wl.startdate >= '2023-07-01 07:00:00') and (wl.startdate < '2023-10-01 07:00:00')
group by wl.author

This returns 93 rows (as expected) but only a total of 430.04 hrs.

---

Is anyone able to explain to me why the addition of a 'group by' would cause this effect?

Thanks in advance for helping a noob...!

[Korsten Bezuidenhout · Accepted solution]

Hi there,

Think you can replace truncate(wl.timeworked/3600, 2) as 'Time (Hrs)' with round(sum((wl.timeworked/3600), 2)) as 'Time (Hrs)'.

Then with the group by wl.author

See if that solves you problem

[Kuzko]

That worked perfectly - thanks KorstenB.
Now to spend some time learning why that worked, but for now I'm underway. Thanks again for taking the time to review & respond!